Сегодня я хочу затронуть тему дополнительных ухищрений, которые могут позволить ускорить выполнение запроса. В данном случае речь пойдёт о перестановке условий в выражениях фильтрации, JOIN'ов, HAVING-клаузах и прочем. Идея заключается в том, что получив негативный результат в одном условии из цепочки выражений, объединенных оператором AND, равно как позитивный результат в одном из условий, объединённых оператором OR, можно не вычислять все последующие и сэкономить вычислительный ресурс. Что это даёт и как конкретно реализовать - об этом ниже.
Время от времени приходится наблюдать запросы со сложными фильтрами наподобие следующего:
SELECT * FROM table
WHERE
date > min_date AND
date < now() - interval '1 day' AND
value IN Subplan AND
id = 42';И на на практике случается, что простая перестановка порядка следования условий в таком выражении позволяет ускорить (а иногда и значительно) время выполнения запроса. Почему? Каждая отдельная операция стоит мало. Однако, если её выполнять снова и снова над миллионами строк из сканируемой таблицы, то затраты на операцию становятся ощутимыми. Особенно если остальные проблемы - подобно попаданию таблицы в shared buffers - уже успешно решены.
Этот эффект хорошо наблюдать на широких таблицах, содержащих десятки столбцов переменной длины. Например, время от времени мне попадается подтормаживающий IndexScan, который тормозит просто из-за того, что поле, по которому выполняется дополнительная фильтрация, является каким-нибудь 20-м по порядку в таблице, и вычисление смещения этого поля от начала строки само по себе требует процессорного времени.
Судя по коду PostgreSQL сообщество уже ранее задумывалось над этим. Коммит 3779f7f, неохотно добавленный T. Lane в 2002 г. чтобы закрыть проблему, добавил реорганизацию выражений (см. order_qual_clauses) таким образом, чтобы поместить все условия содержащие подпланы, в конец списка выражений. Это выглядит логично, поскольку реальная стоимость вычисления сабплана может зависеть от параметров, в него передаваемых, и является дополнительным источником ошибки. В 2007 г. концепция поменялась. С коммитом 5a7471c сортировка выражений выполняется уже исключительно по возрастанию параметра cost - стоимости вычисления каждого конкретного выражения. Эта логика остаётся неизменной до сегодняшнего дня, за исключением небольшой модификации в коммите 215b43c, где изменение кода RLS (Row-Level Security) потребовало управлять порядком вычисления выражений в каждой ноде плана запроса.
Давайте посмотрим, что мы имеем в апстриме по состоянию на сегодняшний день:
CREATE TABLE test (
x integer, y numeric, w timestamp DEFAULT CURRENT_TIMESTAMP, z integer);
INSERT INTO test (x,y) SELECT gs,gs FROM generate_series(1,1E3) AS gs;
VACUUM ANALYZE test;
EXPLAIN (COSTS ON)
SELECT * FROM test
WHERE
z > 0 AND
w > now() AND
x < (SELECT avg(y) FROM generate_series(1,1E2) y WHERE y%2 = x%3) AND
x NOT IN (SELECT avg(y) FROM generate_series(1,1E2) y OFFSET 0) AND
w IS NOT NULL AND
x = 42;Заглядывая в фильтр этого SELECT видим следующую последовательность условий:
Filter: ((w IS NOT NULL) AND (z > 0) AND (x = 42) AND (w > now()) AND
((x)::numeric = (InitPlan 2).col1) AND ((x)::numeric < (SubPlan 1)))В ходе выполнения запроса вычисляться они будут строго последовательно, слева направо. Для справки, стоимости операторов получились следующие:
"
z > 0" - 0.0025"
w > now()" - 0.005"
x < SubPlan 1" - 2.0225"x NOT IN SubPlan 2" - 0.005“
w IS NOT NULL" - 0.0“
x = 42“ - 0.0025
Данный порядок выглядит вполне логичным. Что здесь можно ещё улучшать, спросите вы?
Тут есть как минимум два низковисящих фрукта. Во-первых, можно добавить небольшой кост на порядковый номер колонки, участвующей в выражении. Грубо говоря, чем колонка дальше (правее) в строке таблицы - тем дороже. Оценка не должна быть слишком большой, она нужна оптимизатору для того, чтобы понимать, что вычисление условия x=42 дешевле, чем z=42 при прочих равных.
Второй стандартный паттерн - это когда имеется пара условий с примерно одинаковым костом, например, x=42 и z < 50. Очевидно, что второе условие стоит ставить во вторую позицию, поскольку условие x=42 будет истинным в меньшем количестве случаев и будет реже появляться необходимость вычисления условий, следующих в выражении дальше по списку.
Давайте теперь оценим потенциальный эффект от таких оптимизаций. Есть ли смысл вообще прикладывать даже небольшие усилия? Создадим таблицу, в которой пара колонок будут с одной селективностью, но отстоять далеко друг от друга. А другая пара будет находиться рядом, но иметь разную селективность:
CREATE TEMP TABLE test_2 (x1 numeric, x2 numeric, x3 numeric, x4 numeric);
INSERT INTO test_2 (x1,x2,x3,x4)
SELECT x,(x::integer)%2,(x::integer)%100,x FROM
(SELECT random()*1E7 FROM generate_series(1,1E7) AS x) AS q(x);
ANALYZE;Проверим, чего стоит поиск значения в такой относительно "широкой" строке. Столбцы 
и 
одинаковы во всем, кроме того, что смещение значения столбца 
в строке известно заранее, а для 
его ещё нужно вычислять для каждой строки (1):
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
SELECT * FROM test_2 WHERE x1 = 42 AND x4 = 42;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
SELECT * FROM test_2 WHERE x4 = 42 AND x1 = 42;
/*
Seq Scan on test_2 (actual rows=0.00 loops=1)
Filter: ((x1 = '42'::numeric) AND (x4 = '42'::numeric))
Buffers: local read=94357
Execution Time: 2372.032 ms
Seq Scan on test_2 (actual rows=0.00 loops=1)
Filter: ((x4 = '42'::numeric) AND (x1 = '42'::numeric))
Buffers: local read=94357
Execution Time: 2413.633 ms
*/Получается, что при прочих равных, эффект даже не экстремально длинного тупла дает около 2-3%. Вполне сравнимо с типичным эффектом от использования JIT. Давайте теперь посмотрим на влияние селективности. Колонки 
и 
находятся рядом. разница только в том, что значения в 
почти уникальны, а 
содержит практически одни дубликаты (2):
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
SELECT * FROM test_2 WHERE x2 = 1 AND x1 = 42;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
SELECT * FROM test_2 WHERE x1 = 42 AND x2 = 1;
/*
Seq Scan on test_2 (actual rows=0.00 loops=1)
Filter: ((x2 = '1'::numeric) AND (x1 = '42'::numeric))
Buffers: local read=74596
Execution Time: 2363.903 ms
Seq Scan on test_2 (actual rows=0.00 loops=1)
Filter: ((x1 = '42'::numeric) AND (x2 = '1'::numeric))
Buffers: local read=74596
Execution Time: 2034.873 ms
*/Здесь уже имеем порядка 10%.
Выходит, если принять, что эффект может быть накопительным по дереву плана, а в этом дереве может быть немало как операторов сканирования, так и джойнов, группировок и пр., которые также дадут некоторый процент, то в целом данная методика имеет смысл быть реализованной с учётом незначительного оверхеда на время планирования.
Ок, давайте реализуем и посмотрим, как она заработает. Сделать расширением это смысла не имеет, поскольку до сих пор не существует хука, который позволил бы выполнять операции в момент создания финального плана. Необходимость создания такого хука create_plan_hook внутри функции create_plan() понемногу нарастает, однако Postgres сообщество пока серьёзно этот вопрос не обсуждало.
Если же делать эту фичу в форме патча ядра, то изменения должны быть внесены в два места кода: cost_qual_eval(), где вычисляется стоимость выражений и order_qual_clauses, определяющую правила сортировки выражений. Финальный код можно найти на GitHub в соответствующей ветке.
Если выполнить на этой ветке вышеприведённые примеры, то видно, что выражения выстраиваются в более оптимальном порядке, учитывая порядок следования колонок и селективность. При этом какой-то дополнительный оверхед не заметен.
А как вы считаете, имеет ли смысл изобретать такие микро оптимизации, или нужно мыслить масштабней? Встречались ли вы с подобными проблемами? Оставляйте ваше мнение в комментариях.
THE END.
27 апреля 2025 г., Мадрид, Испания.
