В предыдущей статье я разбирал некоторые нюансы Postgres, касающиеся индексов и параллельных воркеров. Текст этот вызвал достаточно оживленное обсуждение и один из комментаторов предложил значительно более эффективный индекс, нежели те, что были рассмотрены в статье. Механическое сравнение эксплейнов не позволяло понять причины его превосходства и потребовалось дополнительное расследование.

Этот индекс:

CREATE INDEX ON order_events ((event_payload ->> 'terminal'::text),
                              event_type,event_created); -- (1)

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

CREATE INDEX ON order_events (event_created,
                              (event_payload ->> 'terminal'::text),
                              event_type); -- (2)
CREATE INDEX ON order_events (event_created, event_type); -- (3)

Однако реальность такова, что ускорение более чем заметное (см. эксплейны здесь): индекс (1) превосходит (2) более чем в 50 раз, а индекс (3) почти в 25 раз!

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

Однако, если посмотреть в эксплейн, то мы не увидим ничего особенного:

-- (1)
->  Index Scan using order_events_expr_event_type_event_created_idx
      (cost=0.57..259038.66 rows=64540 width=72)
      (actual time=0.095..232.855 rows=204053.00 loops=1)
    Index Cond:
      event_payload ->> 'terminal' = ANY ('{Berlin,Hamburg,Munich}' AND
      event_type = ANY ('{Created,Departed,Delivered}') AND
      event_created >= '2024-01-01 00:00:00+00' AND
      event_created < '2024-02-01 00:00:00+00'
    Index Searches: 9
    Buffers: shared hit=204566

-- (2)
->  Index Scan using order_events_event_created_event_type_expr_idx
      (cost=0.57..614892.22 rows=64540 width=72)
      (actual time=0.499..14303.685 rows=204053.00 loops=1)
    Index Cond:
      event_created >= '2024-01-01 00:00:00+00' AND
      event_created < '2024-02-01 00:00:00+00' AND
      event_type = ANY ('{Created,Departed,Delivered}' AND
      event_payload ->> 'terminal' = ANY ('{Berlin,Hamburg,Munich}')
    Index Searches: 1
    Buffers: shared hit=279131
                        
-- (3)

->  Index Scan using idx_3
      (cost=0.57..6979008.62 rows=64540 width=72)
      (actual time=0.238..8777.846 rows=204053.00 loops=1)
    Index Cond:
      event_created >= '2024-01-01 00:00:00+00' AND
      event_created < '2024-02-01 00:00:00+00' AND
      event_type = ANY ('{Created,Departed,Delivered}')
    Filter: event_payload ->> 'terminal' = ANY ('{Berlin,Hamburg,Munich}')
    Rows Removed by Filter: 4292642
    Index Searches: 1
    Buffers: shared hit=4509185

Допустим, вариант (3) фильтрует много туплов и поэтому медленный. Но даже он, отсеяв 4 млн строк, в два раза быстрее варианта (2), который отличается от (1) только порядком следования колонок в индексе.

Если сравнить сканирования (1) и (2), то единственная заметная разница - 30% отличие в количестве раз, которое индекс потрогал буферные страницы. Но не в 50 же! Значит, эксплейн не показывает нам, где была выполнена основная работа и только значение cost сигнализирует о превосходстве индекса (1).

Однако мы живем в мире ORM и ad-hoc запросов, где сложно выбрать порядок следования колонок в индексе по смыслу хранимых данных, а значит нужно выяснить точно, что здесь происходит и каких данных недостаёт для автоматизированного обнаружения удачного индекса.

Если заглянуть в код оптимизатора, то становится понятно уже в числах, чем индекс (1) так хорош: при прочих равных он из 1 млн. страниц индекса собирается пройти только по 39. Сравним с индексом (2), где также индекс содержит 1 млн. страниц, при этом проходить мы будем по 73 тыс. из них. В туплах индекса это будет 64.5 тыс. против 14 млн. Выходит, что основная работа заключается в том чтобы выбрать строку, вычленить подходящий атрибут и выполнить сравнение.

Вся эта работа никак не отражается в EXPLAIN. Более того, структура IndexScan плана запроса, доступная ядру Postgres и его расширениям после выполнения плана, не содержит никакой полезной информации, позволяющей обнаружить этот объем работы. А значит отсутствует возможность автоматизированного обнаружения неудачного индекса и выбора более оптимального варианта.

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

THE END.
Испания, Торревьеха, 7 августа 2025 года.

Postgres на RTABench Q0: Ещё один заход