Эффективный запрос PostgreSQL по метке времени с использованием сканирования индекса или растрового индекса?

В PostgreSQL у меня есть индекс поля даты в моей таблице tickets. Когда я сравниваю поле с now(), запрос довольно эффективен:

# explain analyze select count(1) as count from tickets where updated_at > now();
                                                             QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=90.64..90.66 rows=1 width=0) (actual time=33.238..33.238 rows=1 loops=1)
   ->  Index Scan using tickets_updated_at_idx on tickets  (cost=0.01..90.27 rows=74 width=0) (actual time=0.016..29.318 rows=40250 loops=1)
         Index Cond: (updated_at > now())
Total runtime: 33.271 ms

Он идет вниз и использует сканирование кучи растровых изображений, если я пытаюсь сравнить его с now() минус интервал.

# explain analyze select count(1) as count from tickets where updated_at > (now() - '24 hours'::interval);
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=180450.15..180450.17 rows=1 width=0) (actual time=543.898..543.898 rows=1 loops=1)
->  Bitmap Heap Scan on tickets  (cost=21296.43..175963.31 rows=897368 width=0) (actual time=251.700..457.916 rows=924373 loops=1)
     Recheck Cond: (updated_at > (now() - '24:00:00'::interval))
     ->  Bitmap Index Scan on tickets_updated_at_idx  (cost=0.00..20847.74 rows=897368 width=0)     (actual time=238.799..238.799 rows=924699 loops=1)
           Index Cond: (updated_at > (now() - '24:00:00'::interval))
Total runtime: 543.952 ms

Есть ли более эффективный способ запроса с использованием арифметики даты?


postgresql sql indexing sql-execution-plan postgresql-performance
person Don Pflaster    schedule 15.07.2014    source источник
comment
Версия? select version(). Опубликуйте схему таблицы \d tickets   -  person Clodoaldo Neto    schedule 15.07.2014
comment
У вас есть ANALYZE стол? Кстати, первый запрос использует только сканирование индекса, в то время как второй обрабатывает гораздо больше записей и должен сканировать саму таблицу.   -  person Ihor Romanchenko    schedule 15.07.2014
comment
@IgorRomanchenko: я вижу сканирование индекса в первом запросе, а не сканирование только индекса.   -  person Erwin Brandstetter    schedule 15.07.2014
comment
40250 строк или 924699 строк, это имеет значение...   -  person Frank Heikens    schedule 15.07.2014
comment
Версия 9.1 (я знаю, пока мы работаем над обновлением :) )   -  person Don Pflaster    schedule 15.07.2014
comment
Основная суть вопроса, однако, заключается в том, почему добавление интервала приводит к тому, что сравнение использует другую стратегию? Разве он не должен просто вычислить СЕЙЧАС() - '24 часа'::интервал, а затем попытаться сравнить это с индексом? Могу ли я обойти это, приведя условие where к чему-то другому?   -  person Don Pflaster    schedule 15.07.2014
comment
Это не дополнение как таковое. Замените now() и now() - '24 hours'::interval соответствующими литералами меток времени, и вы получите тот же результат. Важно ожидаемое количество найденных строк (74 против 897368). Как объяснено в моем ответе.   -  person Erwin Brandstetter    schedule 15.07.2014
comment
Извините, теперь я понял. updated_at › now() возвращает меньше строк. обл. Спасибо всем.   -  person Don Pflaster    schedule 15.07.2014

Ответы (1)


arrow_upward
4
arrow_downward

Первый запрос ожидает найти rows=74, но на самом деле находит rows=40250.
Второй запрос ожидает найти rows=897368 и на самом деле находит rows=924699.

Конечно, обработка в 23 раза большего количества строк занимает значительно больше времени. Так что ваше фактическое время не удивительно.

Статистика для данных с updated_at > now() устарела. Бежать:

ANALYZE tickets;

и повторите свои запросы. А у вас серьезно есть данные с updated_at > now()? Это звучит неправильно.

Однако неудивительно, что статистика устарела для данных, которые были изменены последними. Это по логике вещей. Если ваш запрос зависит от текущей статистики, вы должны запустить ANALYZE перед запуском запроса.

Также проверьте с помощью (только в вашем сеансе):

SET enable_bitmapscan = off;

и повторите свой второй запрос, чтобы увидеть время без сканирования растрового индекса.

Почему растровый индекс сканирует больше строк?

Простое сканирование индекса извлекает строки из кучи последовательно, как они найдены в индексе. Это просто, глупо и без накладных расходов. Быстро для нескольких строк, но может оказаться дороже, чем сканирование растрового индекса с растущим числом строк.

Сканирование растрового индекса собирает строки из индекса перед поиском в таблице. Если несколько строк находятся на одной и той же странице данных, это позволяет избежать повторных посещений и значительно ускорить работу. Чем больше строк, тем больше вероятность, что сканирование растрового индекса сэкономит время.

Для еще большего количества строк (около 5% таблицы, сильно зависит от фактических данных) планировщик переключается на последовательное сканирование таблицы и вообще не использует индекс.

Оптимальным будет сканирование только индекса< /strong>, появившийся в Postgres 9.2. Это возможно только при соблюдении некоторых предварительных условий. Если все соответствующие столбцы включены в индекс, тип индекса поддерживает это, а карта видимости указывает, что все строки на странице данных видны для всех транзакций, эту страницу не нужно извлекать из кучи (таблицы) и информации в указателе достаточно.

Решение зависит от вашей статистики (сколько строк ожидает найти Postgres и их распределение) и от настройки стоимости, особенно random_page_cost, cpu_index_tuple_cost и effective_cache_size.

person Erwin Brandstetter    schedule 15.07.2014
comment
Похоже, мне следует просто обновиться до 9.3 и посмотреть, сохраняется ли проблема. Я должен обновить в любом случае. - person Don Pflaster; 15.07.2014
comment
О, я чувствую себя глупо. Я понимаю update_at › NOW() — я даже не думал об этом, когда играл с запросами. Конечно, рядов меньше. - person Don Pflaster; 15.07.2014