Самый быстрый способ узнать, какие уникальные даты находятся в поле метки времени таблицы?

У меня есть таблица с миллиардами строк. В поле «записано» есть ежедневные разделы, которые представляют собой «отметку времени без часового пояса». Я хочу знать, какие дни в настоящее время находятся в таблице. Я знаю, что мог бы сделать что-то вроде:

SELECT recorded::date
FROM table
GROUP BY 1;

Что в идеале должно работать, но объяснение этого довольно велико и указывает на то, что это займет довольно много времени... если это лучшее, что я могу сделать, я могу принять это (и мы могли бы следить за данными, поскольку они входит), но мне было интересно, может ли быть более эффективный способ сделать это, учитывая, что у меня ежедневное разбиение?


postgresql greenplum
person A Question Asker    schedule 17.05.2011    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Вы можете создать индекс примерно так:

create index your_index_name
on table (date_trunc('day', recorded))

В моем тесте PostgreSQL 9.something использовал последовательное сканирование перед добавлением индекса, последовательное сканирование после простого индексирования столбца «записано» и сканирование индекса после его индексирования с помощью date_trunc(). Выбор строк за один день занял 66 мс без индекса, 68 мс с простым индексом и 13 мс с индексом с использованием date_trunc().

С миллиардами строк ожидайте, что создание этого индекса займет несколько минут. (кашель)

person Mike Sherrill 'Cat Recall'    schedule 18.05.2011

arrow_upward
1
arrow_downward

Здесь есть очень похожая тема:

Медленный выбор отдельного запроса на postgres

Если вы знаете минимальные/максимальные даты, вам будет лучше запрашивать список дат, чем выполнять последовательное сканирование всей таблицы. Предполагая, что у вас есть записанный индекс, что-то вроде этого должно быть быстрее:

with days as (
select date_trunc('day', min(recorded))::date + k * interval '1 day' as day
from records,
     generate_series(0,
                    (select date_trunc('day', max(recorded))::date
                            - date_trunc('day', min(recorded)::date
                    from records
     )) as k
)
select day
from days
where exists (
      select 1
      from records
      where day <= recorded and recorded < day + interval '1 day'
      );

Может потребоваться несколько изменений в приведенном выше запросе, но общая идея такова: будет быстрее выполнить несколько тысяч сканирований подзапросов/индексов в индексированном поле, чем последовательное сканирование нескольких миллиардов строк и объединить их, чтобы идентифицировать отдельные дни.

person Denis de Bernardy    schedule 18.05.2011
comment
Если таблица разделена на один раздел в день, вам даже не нужен индекс даты, поскольку исключение ограничения выберет правильную таблицу, а первая строка, прочитанная из таблицы, будет удовлетворять EXISTS. - person bobflux; 18.05.2011