Как postgres решает, следует ли использовать сканирование индекса или последовательное сканирование?

explain analyze показывает, что postgres будет использовать сканирование индекса для моего запроса, который извлекает строки и выполняет фильтрацию по дате (т. е. 2017-04-14 05:27:51.039):

explain analyze select * from tbl t where updated > '2017-04-14 05:27:51.039';
                                                          QUERY PLAN                                                          
 -----------------------------------------------------------------------------------------------------------------------------
  Index Scan using updated on tbl t  (cost=0.43..7317.12 rows=10418 width=93) (actual time=0.011..0.515 rows=1179 loops=1)
    Index Cond: (updated > '2017-04-14 05:27:51.039'::timestamp without time zone)
  Planning time: 0.102 ms
  Execution time: 0.720 ms

однако выполнение того же запроса, но с другим фильтром даты '2016-04-14 05:27:51.039' показывает, что вместо этого postgres будет выполнять запрос с использованием последовательного сканирования:

explain analyze select * from tbl t where updated > '2016-04-14 05:27:51.039';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on tbl t  (cost=0.00..176103.94 rows=5936959 width=93) (actual time=0.008..2005.455 rows=5871963 loops=1)
   Filter: (updated > '2016-04-14 05:27:51.039'::timestamp without time zone)
   Rows Removed by Filter: 947
 Planning time: 0.100 ms
 Execution time: 2910.086 ms

Как postgres решает, что использовать, особенно при фильтрации по дате?


postgresql sql-execution-plan explain
person lolski    schedule 15.04.2017    source источник

Ответы (2)


arrow_upward
7
arrow_downward

Планировщик запросов Postgres основывает свои решения на оценках затрат и статистике столбцов, которые собираются ANALYZE и по возможности некоторыми другими служебными командами. Все это происходит автоматически, когда autovacuum включено (по умолчанию).

Руководство:

Большинство запросов извлекают только часть строк в таблице из-за предложений WHERE, которые ограничивают количество проверяемых строк. Таким образом, планировщик должен оценить избирательность предложений WHERE, то есть долю строк, соответствующих каждому условию в предложении WHERE. Информация, используемая для этой задачи, хранится в pg_statistic системном каталоге. Записи в pg_statistic обновляются командами ANALYZE и VACUUM ANALYZE и всегда являются приблизительными, даже если они обновляются недавно.

Есть количество строк (в pg_class), список наиболее распространенных значений и т.д.

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

Как правило, это сканирование индекса -> сканирование растрового индекса -> последовательное сканирование, тем больше строк будет извлечено.

Для вашего конкретного примера важной статистикой является histogram_bounds, которая дает Postgres приблизительное представление о том, сколько строк имеют большее значение, чем заданное. Есть более удобный вид pg_stats для человеческого глаза:

SELECT histogram_bounds
FROM   pg_stats
WHERE  tablename = 'tbl'
AND    attname = 'updated';

В руководстве есть отдельная глава, посвященная оценке строк.

person Erwin Brandstetter    schedule 15.04.2017

arrow_upward
2
arrow_downward

Очевидно, оптимизация запросов сложна. Этот ответ не предназначен для погружения в особенности оптимизатора Postgres. Вместо этого он предназначен для того, чтобы дать вам некоторое представление о том, как принимается решение об использовании индекса.

По оценкам, ваш первый запрос вернет 10 418 строк. При использовании индекса происходят следующие операции:

  • Механизм использует индекс, чтобы найти первое значение, удовлетворяющее условию.
  • Затем движок перебирает значения, заканчивая работу, когда условие перестает быть истинным.
  • Затем для каждого значения в индексе механизм ищет данные на странице данных.

Другими словами, при использовании индекса возникают небольшие накладные расходы — инициализация индекса и последующий поиск каждой страницы данных по отдельности.

Когда движок выполняет полное сканирование таблицы:

  • Начинается с первой записи на первой странице
  • Выполняет сравнение и принимает или отклоняет запись
  • Продолжается последовательно через все страницы данных

Дополнительных накладных расходов нет. Кроме того, механизм может «предварительно загружать» следующие страницы для сканирования во время обработки текущей страницы. Такое перекрытие операций ввода-вывода и обработки — большая победа.

Я пытаюсь подчеркнуть, что найти баланс между этими двумя понятиями может быть непросто. Где-то между 10 418 и 5 936 959 Postgres решает, что накладные расходы индекса (и выборка страниц случайным образом) стоят больше, чем просто сканирование всей таблицы.

person Gordon Linoff    schedule 15.04.2017