Кластеризуем миллионы планов PostgreSQL

Как найти самые "горячие" запросы на вашем PostgreSQL-сервере? Поискать их в логе и проанализировать план или воспользоваться расширением pg_stat_statements.

А если в лог попадает миллион запросов за сутки?.. Тогда любое значение лимита pg_stat_statements.max окажется недостаточно велико, чтобы собрать правдивую статистику. Так давайте собирать эту статистику прямо с планов!

Но для некоторых сервисов СБИС нам в "Тензоре" производительность запросов к базе настолько важна, что auto_explain.log_min_duration приходится выставлять в единицы миллисекунд - и вот они, миллионы планов... Как не потеряться в них?

От планов - к шаблонам

Давайте сначала проведем небольшой эксперимент на таблице с сильно неоднородными данными:

CREATE TABLE tst AS
SELECT
  *
, CASE
    WHEN r < 0.9    THEN 0
    WHEN r < 0.99   THEN 1
    WHEN r < 0.999  THEN 2
    WHEN r < 0.9999 THEN 3
  END v
FROM
  (
    SELECT
      i
    , random() r
    FROM
      generate_series(1, 1e4) i
  ) T;

CREATE INDEX ON tst(v);
ANALYZE tst;

Посмотрим на план одного и того же запроса при v = 0:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT
  *
FROM
  tst
WHERE
  v = 0;

Seq Scan on tst (actual time=0.023..1.236 rows=9034 loops=1)
  Filter: (v = 0)
  Rows Removed by Filter: 966
  Buffers: shared hit=64

... v = 1:

Bitmap Heap Scan on tst (actual time=0.074..0.286 rows=876 loops=1)
  Recheck Cond: (v = 1)
  Heap Blocks: exact=64
  Buffers: shared hit=69
  ->  Bitmap Index Scan on tst_v_idx (actual time=0.058..0.058 rows=876 loops=1)
        Index Cond: (v = 1)
        Buffers: shared hit=5

... v = 2:

Bitmap Heap Scan on tst (actual time=0.047..0.106 rows=83 loops=1)
  Recheck Cond: (v = 2)
  Heap Blocks: exact=45
  Buffers: shared hit=47
  ->  Bitmap Index Scan on tst_v_idx (actual time=0.031..0.031 rows=83 loops=1)
        Index Cond: (v = 2)
        Buffers: shared hit=2

и v = 3, наконец:

Index Scan using tst_v_idx on tst (actual time=0.020..0.026 rows=6 loops=1)
  Index Cond: (v = 3)
  Buffers: shared hit=8

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

-- v = 0
Seq Scan on tst

-- v = 1, v = 2
Bitmap Heap Scan on tst
  ->  Bitmap Index Scan on tst_v_idx

-- v = 3
Index Scan using tst_v_idx on tst

Ровно это делает и наш сервис визуализации планов, когда помогает вам анализировать запрос:

Такая методика позволяет миллионы планов превращать в сотни шаблонов.

От шаблонов - к моделям

Но подождите, у нас был один и тот же запрос "на входе", а "на выходе" мы получили аж 3 разных шаблона! И если мы будем смотреть сводную статистику по "топовым" шаблонам, то наверняка упустим этот запрос, ведь его показатели будут "размазаны" по нескольким записям.

Но ведь, во всех шаблонах мы видим, что производилось сканирование таблицы tst, только разными способами: Seq Scan, Bitmap Scan и Index Scan - и это лишь одна из причин "разбегания" шаблонов, а их много больше:

• порядок и вид соединения таблиц (Nested Loop, Hash Semi Join, Merge Join, ...)

• наличие/отсутствие/несовпадение алиасов у одних и тех же таблиц

• разные виды чтений (Index Only Scan / Index Scan / Bitmap Index Scan) по индексам одной и той же таблицы

• использование параллелизации при извлечении данных (Seq Scan vs Parallel Seq Scan)

• наличие/отсутствие вспомогательной сортировки / группировки / уникализации для WindowAgg

• сработка и порядок триггеров, особенно условных

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

• сохраняем общую структуру плана (CTE / InitPlan / SubPlan / Append)

• объединяем разные операции над таблицей в Scan Table

• вместо множества вложенных Join/Loop-узлов, включая сортировки и хэширование, оставляем один Join со отсортированным списком участвующих таблиц

• дополнительные узлы обработки данных вроде сортировки, группировки и уникализации превращаем в общий узел Process

И если все эти преобразования провести над нашими шаблонами, мы получим для них единую модель:

Scan Table tst

Ее можно увидеть на соответствующей вкладке анализа плана. При этом план с достаточно сложной структурой:

... может превратиться во вполне компактное представление:

Process
  InitPlan 1
    ->  Process
          ->  Scan Table "request-statuses"
  InitPlan 2
    ->  Process
          ->  Scan Table "request-statuses"
  ->  Join
        ->  Scan Table "request-statuses"
        ->  Scan Table "task-tracker-times"
        ->  Scan Table requests
        ->  Scan Table tasks

Сотни шаблонов уже превращаются в десятки моделей.

Кластерный анализ планов

Но сами модели могут все-таки немного отличаться друг от друга для "родственных" запросов. Что-то вроде этого:

->  Scan Table tst         | A
      ->  InitPlan 1       | B
            -> Scan Values | B

->  Scan Table tst         | A
      SubPlan 1            | C
        ->  Scan Table tst | C

Как определить метрику "похожести" двух моделей? Можно, конечно, подключить какую-то зубодробительную математику, нейронные сети, суперкомпьютеры...

А можно подойти алгоритмически и выделить у обеих моделей построчно общий префикс A . Тогда полные модели B и C можно считать его потомками и собрать так иерархию представления для всех моделей сервера.

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