PostgreSQL Antipatterns: рекурсивные грабли на ровном месте, или Сказка о потерянном времени

В моей практике ускорения SQL-запросов для PostgreSQL, в большинстве случаев, все сводится к применению типовых методик — их не особенно-то и много, и прочитать про большинство из них можно в моем профиле.

Но иногда обнаруживаются очень странные вещи в поведении этой, безусловно, отличной СУБД.

Все началось с запроса, который мне показали с диагнозом «необъяснимо тормозит»:

EXPLAIN ANALYZE WITH RECURSIVE ttree AS ... -- ... TABLE ttree;

В самом запросе не было ничего особо интересного, никаких wCTE, только попытка реализовать оптимизированный проход вниз по иерархии, а вот в плане — еще как:

CTE Scan on ttree  (actual time=0.036..0.243 rows=4 loops=1)   CTE ttree ... Planning time: 0.594 ms Execution time: 413.393 ms

То есть за половину миллисекунды отработал планировщик, еще четверть заняло фактическое выполнение… и еще 412ms или 99.8% всего времени — непонятно что.

Вычленяем минимальный запрос

Если в запросе что-то ведет себя странно — начинаем отсекать «хвост» по частям, пока ситуация не станет понятной.

Смотрим на конец запроса:

-- ... , tlist AS (   SELECT * FROM thiers ) TABLE ttree;

Понятно, что когда wCTE в запросе нет, то генерация такой CTE, у которой нет ни одного потребителя, даже в план не попадает. Но, на всякий случай закомментируем:

-- ... /* , tlist AS (   SELECT * FROM thiers ) */ TABLE ttree;

Запрос выполняется по-прежнему, но теперь уже ничуть не тормозит:

CTE Scan on ttree  (actual time=0.025..0.240 rows=4 loops=1)   CTE ttree ... Planning time: 0.289 ms Execution time: 0.316 ms

Внезапно! Как и почему от комментирования неиспользуемой CTE запрос ускорился в 500 раз?

Борьба за воспроизводимость

Итак, мы неожиданно выяснили, что «тормозить» перестает, если убрать лишнюю CTE, с обращением к другой CTE — thier. Вот только эта CTE тоже не простая, а рекурсивная.

Возьмем тестовую микротабличку:

CREATE TABLE tbl AS SELECT   (random() * 1e5)::integer val FROM   generate_series(1, 1e5);    CREATE INDEX ON tbl(val);

И постепенное отсечение всех элементов приводит нас к вот такой модели запроса:

EXPLAIN (ANALYZE, COSTS OFF) -- первая рекурсия, возвращает 0 "записей" из таблицы WITH RECURSIVE R1 AS (   SELECT     '{}'::tbl[] recs UNION ALL   SELECT     '{}'::tbl[]   FROM     R1   WHERE     coalesce(recs, '{}') <> '{}' ) -- "разворачиваем" якобы полученные записи в поля , R1v AS (   SELECT     (rec).val   FROM     (       SELECT         unnest(recs) rec       FROM         R1     ) T ) -- вторая рекурсия , R2 AS (   TABLE R1v UNION   SELECT     R1v.*   FROM     R1v   , R2 ) -- неиспользуемый результат второй рекурсии , X AS (   TABLE R2 ) TABLE R1;

Вполне логично, что все узлы, кроме первой рекурсии, не исполнялись — (never executed). Но вот итоговый план потянул почти на 600ms, вернув гордое «ничего из ничего».

Это поведение вполне стабильно и дает на версии PostgreSQL 10.19 воспроизводимый результат порядка 400-600 «лишних» миллисекунд.

Что интересно, на PostgreSQL 13.5 результат гораздо стабильнее, но не лучше — 600-615ms, а вот на случайно оказавшейся 13b2 на Win10 — неожиданно, всего 3ms!

Надеюсь, кто-то сможет «копнуть» ситуацию глубже и выложит детальный разбор причин, а пока что запомним: две рекурсии в одном запросе — проблемы. Кто предупрежден — вооружен!