Время от времени приходится слышать мнение, что Postgres никуда не годится для решения задач аналитики. При при этом, в качестве аргументации приводятся в пример результаты тестирования на TPC‑H или ClickBench. Что ж, когда стоит простая задача перебрать 100 млн строк на диске и посчитать набор агрегатов над ними — формат хранения и распараллеливания действительно сильно ограничивают нас в возможностях оптимизации СУБД. Однако когда запросы высоко селективны, им по факту требуется не так много строк таблицы и фокус внимания смещается на порядок JOINов, кэширование промежуточных результатов и минимизацию операций сортировки. В этом случае Postgres, имеющий весьма широкий выбор различных стратегий выполнения запроса, может получить преимущество...

Я захотел посмотреть, можно ли улучшить работу Postgres, если сильно постараться и использовать весь доступный инструментарий. Бенчмарк RTABench — достаточно свежий, заявленный как 'приближенный к реальной жизни и высокоселективный'. Ещё один его плюс — в запросах встречаются выражения над непростым в обработке типом JSONB. Да и результаты Postgres на RTABench выглядят не очень. В общем, я решил пройтись по всем его запросам (благо их не очень много) и посмотреть, что там можно оптимизировать. Однако уже на нулевом запросе возникло достаточное количество нюансов, чтобы это стоило вынести в отдельное обсуждение.

Сетап у меня не самый свежий — Macbook Pro 2019, ещё на процессоре Intel, ждать впечатляющих и стабильных цифр не приходится. Поэтому попробуем обращать внимание на качественные, а не количественные характеристики. И для такой цели моей аппаратной платформы должно быть достаточно. Список нестандартных настроек инстанса Postgres можно взять здесь.

Итак, дано: нулевой запрос, который представляет собой вычисление нескольких агрегатов над относительно небольшой выборкой из таблицы:

EXPLAIN (ANALYZE, BUFFERS ON, TIMING ON, SETTINGS ON)
WITH hourly_stats AS (
  SELECT 
    date_trunc('hour', event_created) as hour,
    event_payload->>'terminal' AS terminal,
    count(*) AS event_count
  FROM order_events
  WHERE 
    event_created >= '2024-01-01' AND
    event_created < '2024-02-01'
    AND event_type IN ('Created', 'Departed', 'Delivered')
  GROUP BY hour, terminal
)
SELECT 
  hour,
  terminal,
  event_count,
  AVG(event_count) OVER (
    PARTITION BY terminal
    ORDER BY hour
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) AS moving_avg_events
FROM hourly_stats
WHERE terminal IN ('Berlin', 'Hamburg', 'Munich')
ORDER BY terminal, hour;

Фаза 0 ('дефолтное выполнение')

Вроде бы ничего сложного. Выполним запрос над дефолтной схемой данных (EXPLAIN почищен):

WindowAgg  (actual time=21053.119 rows=2232)
  Window: w1 AS (PARTITION BY
   order_events.event_payload ->> 'terminal'
   ORDER BY date_trunc('hour', order_events.event_created))
  Buffers: shared read=3182778
  -> Sort  (actual time=21052.476 rows=2232)
     Sort Key:
      order_events.event_payload ->> 'terminal',
      date_trunc('hour', order_events.event_created)
      Sort Method: quicksort  Memory: 184kB
   -> GroupAggregate  (actual time=21051.875 rows=2232)
       Group Key:
        date_trunc('hour', order_events.event_created),
        order_events.event_payload ->> 'terminal'
     -> Sort (actual time=21037.609..21042.766 rows=204053)
         Sort Key:
          date_trunc('hour', order_events.event_created),
          order_events.event_payload ->> 'terminal'
         Sort Method: quicksort  Memory: 12521kB
       -> Bitmap Heap Scan on order_events (actual time=20999.978 rows=204053)
          Recheck Cond: event_type = ANY ('{Created,Departed,Delivered}')
          Filter:
           event_created >= '2024-01-01 00:00:00+00' AND
           event_created < '2024-02-01 00:00:00+00' AND
           event_payload ->> 'terminal') = ANY ('{Berlin,Hamburg,Munich}')
          Rows Removed by Filter: 57210049
          Heap Blocks: exact=3133832
          Buffers: shared read=3182778
        -> Bitmap Index Scan (actual time=1683.357 rows=57414102)
           Index Cond: event_type = ANY ('{Created,Departed,Delivered}')
           Index Searches: 1
           Buffers: shared read=48946
Execution Time: 21060.564 ms

Время выполнения 21 секунда? Что‑то слишком медленно. Основная загвоздка здесь в том, что у нас по‑умолчанию есть только один очень неудачный, малоселективный индекс. По эксплейну видно, что большая часть работы здесь — это сбор идентификаторов (tid) строк‑кандидатов — 1.6c, а затем проход по этим строкам и фильтрация 98% прочитанных строк — 19с.

Первая засада здесь обнаружилась сразу. Я выделил под shared_buffers 8GB, однако СУБД позволяет занять под одну таблицу только некоторую долю этих буферов. Формула там достаточно сложная и многофакторная, но в базе лежит соотношение NBuffers / MaxBackends. В итоге, с моими настройками Postgres выделит не более 2.4 GB на одну таблицу. Так что денормализация всей базы в одну широкую и длинную таблицу уже поэтому является в Postgres сомнительной идеей.

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

Весьма странный график. Очевидно, что если большая часть работы - это чтение с диска и деформация тупла, то значительного эффекта от параллелизма ждать не приходится. Но откуда этот скачок между 6 и 7 параллельными процессами? Заглянув в EXPLAIN'ы можно легко понять - произошло изменение плана запроса. На малом количестве воркеров использовался BitmapScan, а на большем количестве уже доминирует SeqScan.

Так может быть SeqScan стоило использовать и на малом количестве воркеров? Посмотрим, как ускоряется выполнение операции сканирования отдельно для BitmapScan и SeqScan, и посмотрим также, как меняются стоимости нод сканирования (в относительных величинах):

Абсолютные цифры и эксплейны можно посмотреть здесь и здесь.

Дело оказалось в том, что на малом количестве воркеров BitmapScan имеет меньший кост по отношению к SeqScan, что в нашем случае не коррелирует с execution time. Дело тут видимо в тонком балансе: меньше строк прочитал / но чаще ходил из индекса в таблицу. Точнее сказать сложно, поскольку explain не показывает таких деталей эстимации, как предполагаемое количество строк, поднятое с диска перед фильтрацией, оценка количества пофетченых дисковых страниц или оценка доли страниц, которые сразу обнаружатся в shared_buffers. С другой стороны, кост модель предполагает лучшую масштабируемость SeqScan в сравнении с BitmapScan, что и вызывает переключение плана на SeqScan. Учитывая, что для SeqScan изменение величины cost предсказывает неоправданно большой прирост производительности, это может закончиться сваливанием в SeqScan там, где не следует. Таким образом, не следует пока увлекаться параллельными воркерами при оптимизации запросов.

Фаза 1 (' Типовая оптимизация')

Теперь пойдем далее и посмотрим, что даст Postgres хороший индекс. Типовой практикой является создание индекса по наиболее часто используемой в фильтрах высокоселективной колонке. Для данного запроса выбор ограничивается только одним вариантом:

CREATE INDEX idx_1 ON order_events (event_created);

С таким индексом оптимизатор использует IndexScan для доступа к данным, а время выполнения запроса (случай без параллельных воркеров) снижается до 6.5с. Любопытно, что предыдущий план запроса трогал буферные страницы 3 млн. раз (shared read=3182778), а в данном примере, при точечной выборке — уже 14 млн. (shared hit=14317527). Хождений в буфер стало больше, однако раньше каждая страница вытесняла предыдущую из буфера, тогда как сейчас и индекс и необходимые ему дисковые страницы поместились в shared buffers, что и привело к ускорению.

Посмотрим, помогут ли здесь параллельные воркеры, а заодно посмотрим, как это предсказывает cost model:

сырые данные можно взять здесь.

Да, мы наблюдаем некоторое ускорение. Эффект распараллеливания заходит так далеко, что приходится расширить допустимое количество воркеров до 24, чтобы отследить эффект до конца. И здесь проявился один из минусов Postgres — хотя мы выставляем все возможные гуки в большие величины, на таблице order_events оптимизатор встал в намертво зашитый лимит по количеству воркеров (10 для данной таблицы), и пришлось его обходить командой:

ALTER TABLE order_events SET (parallel_workers = 32);

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

Ещё один факт можно извлечь из вышеприведённого графика — cost‑модель для Index Scan весьма консервативна: при максимальном наблюдаемом ускорении 8, модель не показала даже двукратного ускорения. Отсюда выводы: 1) используя индексы не стоит стесняться поднимать лимиты по воркерам и 2) Ноды сканирования, более чувствительные к количеству воркеров (мы наблюдали такое, например, с SeqScan), могут неожиданно триггернуть перестроение плана запроса в худшую сторону.

Однако, текущий индекс - это не предел мечтаний. Присмотримся к ноде сканирования повнимательнее:

Index Scan (actual time=6555.122 rows=204053)
  Index Cond: event_created >= '2024-01-01 00:00:00+00' AND
    event_created < '2024-02-01 00:00:00+00'
  Filter: event_type = ANY ('{Created,Departed,Delivered}' AND
    event_payload ->> 'terminal' = ANY ('{Berlin,Hamburg,Munich}')
  Rows Removed by Filter: 14099758
  Index Searches: 1
  Buffers: shared hit=14317527

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

Фаза 2 ('Усиленная оптимизация')

Здесь мы уже не будем стесняться и предположим, что имеется некий 'index adviser', дающий возможность в (аналитическом) проде находить и автоматически строить составные индексы. Эти индексы в свою очередь позволяют минимизировать чтение строк таблиц, адаптируя всю систему под входящую нагрузку.

В данном запросе вариантов у нас достаточно немного. Откидывая GIN Index, поскольку поле event_payload не очень селективно, имеем два варианта:

CREATE INDEX idx_2 ON order_events (event_created, event_type)
  INCLUDE (event_payload);
CREATE INDEX idx_3 ON order_events (event_created, event_type);

Вариант idx_2 не потребует хождения в таблицу вообще, а idx_3 может триггернуть как IndexScan, так и BitmapScan. Различные варианты эксплейнов с этими индексами можно найти здесь. Любопытно, что при наличии ранее созданного индекса idx_1 добавление idx_2 не приводит к переключению на очевидно более быстрый IndexOnlyScan. Судя по всему, дело здесь в том, что при оценке стоимости индексного доступа большой вес имеет ширина индекса. А jsonb поле вероятно сильно увеличило размер idx_2.

В итоге, наиболее оптимальным получился индекс idx_3 по соотношению компактность / количество селектируемых записей с методом сканирования BitmapScan. Если присмотреться к нодам сканирования, то можно понять почему:

Bitmap Heap Scan (actual time=1286.430 rows=204053
  Rows Removed by Filter: 4292642
  Heap Blocks: exact=269237
  Buffers: shared hit=313925
  Bitmap Index Scan (actual time=625.170 rows=4496695)
    Index Searches: 1
    Buffers: shared hit=44688

Index Only Scan (actual time=1586.097 rows=204053)
  Rows Removed by Filter: 4292642
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=2558314

Index Scan (actual time=2847.517 rows=204053)
  Rows Removed by Filter: 4292642
  Index Searches: 1
  Buffers: shared hit=4509185

Все трое возращают одинаковое количество строк, выполняют один проход по индексу, фильтруют одинаковое количество строк. Однако, IndexOnlyScan выигрывает у Index Scan за счет того, что не заходя в таблицу трогает буферные страницы в два раза реже (2.6 млн. V/S 4.5 млн.); BitmapScan же ходит в буфер ещёреже (300 тыс. раз) — пройдя по индексу и собрав tid строк-кандидатов, он дальше идет точечно в хип трогая каждую потенциально полезную страницу только один раз.

Давайте посмотрим, как теперь параллельные воркеры помогаю ускорить запрос для каждого из видов сканирования:

Получаем, что в случае BitmapScan нет особого смысла задействовать воркеры. Имея значительный вычислительный ресурс и невысокую конкуренцию между клиентами стоит подумать о снижении стоимости параллельного выполнения и отключении BitmapScan.

Однако же cost-модель опять оказалась нечувствительна к эффекту параллелизма. И в первую очередь стоит что-то сделать здесь. Также было замечено, что на числе воркеров 8+ опять происходило перестроение плана на SeqScan, что привело к увеличению времени выполнения с ~1c до ~21c. Поэтому в интересах эксперимента  SeqScan пришлось вручную отключить.

Однако, даже с таким хорошим индексом мы видим, что некоторое количество строк приходится фильтровать. Пойдем же до конца и организуем точечный доступ к только релевантным данным.

Фаза 3 ('Максимальная оптимизация')

Попробуем теперь достичь теоретического предела оптимизации данного запроса. Здесь мы можем пофантазировать, что у нас есть продвинутый  'Disk Access Tuner', который может анализировать различные выражения SQL запроса и находить комбинации высоко и низко селективных фильтров на таблице — хороший повод подумать про частичные индексы.

Создадим следующий идеальный индекс:

CREATE INDEX idx_5 ON order_events (event_created, event_type)
INCLUDE (event_payload)
WHERE
  event_created >= '2024-01-01' and event_created < '2024-02-01' AND
  event_type IN ('Created', 'Departed', 'Delivered') AND
  (event_payload ->> 'terminal'::text) = ANY ('{Berlin,Hamburg,Munich}'::text[]);

Индекс построен таким образом, что обращение в таблицу не требуется совсем, и все строки в этом индексе являются релевантными запросу. Таким образом, даже нет необходимости вычислять значение фильтра, что также экономит процессорное время. Базовый вариант (без воркеров) теперь выполняется за 54 мс (см. эксплейны здесь).

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

Index Only Scan (cost=0.42..4998.13 rows=70210)
                (actual time=34.718 rows=204053.00 loops=1)
    Heap Fetches: 0
    Index Searches: 1
    Buffers: shared hit=110862

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

Давайте же напоследок посмотрим, работает ли здесь масштабирование за счёт параллельных воркеров. Ввиду увеличившейся доли остальной (не параллельной) части запроса будем смотреть только в actual time и estimated cost непосредственно ноды сканирования:

Здесь, как и в предыдущий раз видно, что эффект сканирования IndexOnlyScan заканчивается на трёх воркерах. Но модель стоимости не показывает даже этого.

Почему cost модель так плохо отражает реальные цифры? Возможно, что она консервативна в силу разнообразия аппаратных моделей параллелизма и, как следствие, эффектов от распараллеливания. А может быть там неявно закладывается предположение, что рядом есть соседний бэкенд, который будет конкурировать за ресурс? Как бы то ни было, лично мне хотелось бы иметь явный параметр, позволяющий настраивать этот эффект зная характер нагрузки моей системы.

Выводы

Какие уроки можно вынести из этого простого эксперимента?

1. Параллельные воркеры ощутимо сказываются на производительности. Стоит корректировать кост-модель оптимизатора под возможности сервера, увеличивая долю параллельных планов и количество воркеров.

2. Эффективность распараллеливания зависит от метода доступа. Стоит отдавать предпочтение IndexScan.

3. По видимому, кост-модель параллелизма в Postgres ещё не приглажена достаточно и стоит опасаться побочных эффектов. Например, сваливания в неэффективный SeqScan.

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

5. Можно искать точки повышения эффективности планирования запросов в уменьшении ошибок оптимизации за счёт анализа индексов по таблице.

Спасибо за внимание!

THE END
Испания, Мадрид, 26 июля 2025 года.