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

Если вы посмотрите на PG16 release notes, то увидите некоторые из этих улучшений планировщика. Но из-за объема изменений, вносимых в каждом выпуске PostgreSQL, невозможно предоставить достаточно подробную информацию о каждом изменении.

В этом посте вы получите глубокое представление о 10 улучшениях, внесенных в планировщик запросов PostgreSQL 16. Для каждого из улучшений будет сравнения выходных данных планировщика PG15 и PG16, а также примеры того, что изменилось, в виде автономного теста, который вы можете попробовать сами.

1. Разрешена инкрементальная сортировка в большем количестве случаев, включая DISTINCT (David Rowley)

Have the planner consider Incremental Sort for DISTINCT

Инкрементальные сортировки впервые были добавлены в PostgreSQL 13. Они сокращают усилия, необходимые для получения отсортированных результатов. Каким образом? Используя знание о том, что данный результирующий набор уже отсортирован по 1 или более ведущих столбцов, и выполняя сортировку только по оставшимся столбцам.

Например, если в столбце есть индекс btree a и нам нужны строки, упорядоченные по a иb, тогда мы можем использовать индекс btree (который предоставляет предварительно отсортированные результаты по столбцу a) и сортировать просмотренные строки только при изменении значения a. Благодаря алгоритму быстрой сортировки(quicksort), сортировка множества небольших групп более эффективна, чем сортировка одной большой группы.

Планировщик запросов PostgreSQL 16 теперь предусматривает выполнение инкрементальной сортировки для SELECT DISTINCT запросов. До PG16 при выборе метода сортировки для запросов SELECT DISTINCT планировщик учитывал только выполнение полной сортировки (что дороже, чем инкрементальная сортировка).

-- Setup
CREATE TABLE distinct_test (a INT, b INT);
INSERT INTO distinct_test
SELECT x,1 FROM generate_series(1,1000000)x;
CREATE INDEX on distinct_test(a);
VACUUM ANALYZE distinct_test;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT DISTINCT a,b FROM distinct_test;

PG15 EXPLAIN output

                          QUERY PLAN
---------------------------------------------------------------
 HashAggregate (actual rows=1000000 loops=1)
   Group Key: a, b
   Batches: 81  Memory Usage: 11153kB  Disk Usage: 31288kB
   ->  Seq Scan on distinct_test (actual rows=1000000 loops=1)
 Planning Time: 0.065 ms
 Execution Time: 414.226 ms
(6 rows)

PG16 EXPLAIN output

                          QUERY PLAN
------------------------------------------------------------------
 Unique (actual rows=1000000 loops=1)
   ->  Incremental Sort (actual rows=1000000 loops=1)
         Sort Key: a, b
         Presorted Key: a
         Full-sort Groups: 31250  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
         ->  Index Scan using distinct_test_a_idx on distinct_test (actual rows=1000000 loops=1)
 Planning Time: 0.108 ms
 Execution Time: 263.167 ms
(8 rows)

В выводе PostgreSQL 16 EXPLAIN видно, что планировщик решил использовать индекс distinct_test_a_idx для столбца a, а затем выполнил Incremental Sort, чтобы отсортировать все равные значения a по b. На это указывает Presorted Key: a. Поскольку приведенные выше операторы INSERT добавили только одно значение b для каждого значения a, каждая группа кортежей, отсортированная с помощью инкрементальной сортировки, содержит только одну строку.

Вывод EXPLAIN для PostgreSQL 16 показывает, что Peak Memory для Incremental Sort был всего 26kB, в то время как методу хэширования, используемому PostgreSQL 15, требовалось много памяти, настолько много, что на диск приходилось загружать 31288kB. Запрос в PostgreSQL 16 выполняется на 63% быстрее.

2. Добавлена возможность для агрегатов, имеющих ORDER BY или DISTINCT, использовать предварительно отсортированные данные (David Rowley)

Improve performance of ORDER BY / DISTINCT aggregates

В PostgreSQL 15 и более ранних версиях агрегатные функции, содержащие ORDER BY или DISTINCT, приводили к тому, что исполнитель(executor) всегда выполнял сортировку внутри Aggregate. Поскольку сортировка выполнялась всегда, планировщик никогда не пытался сформировать план предоставления предварительно отсортированных входных данных для объединения строк по порядку.

Планировщик PostgreSQL 16 теперь пытается сформировать план, который передает строки в Aggregate в правильном порядке. А исполнитель теперь достаточно умен, чтобы понять это и отказаться от выполнения сортировки, когда строки уже предварительно отсортированы в правильном порядке.

-- Setup
CREATE TABLE aggtest (a INT, b text);
INSERT INTO aggtest SELECT a,md5((b%100)::text) FROM generate_series(1,10) a, generate_series(1,100000)b;
CREATE INDEX ON aggtest(a,b);
VACUUM FREEZE ANALYZE aggtest;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS)
SELECT a,COUNT(DISTINCT b) FROM aggtest GROUP BY a;

PG15 EXPLAIN output

                          QUERY PLAN
---------------------------------------------------------------
 GroupAggregate (actual rows=10 loops=1)
   Group Key: a
   Buffers: shared hit=892, temp read=4540 written=4560
   ->  Index Only Scan using aggtest_a_b_idx on aggtest (actual rows=1000000 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=892
 Planning Time: 0.122 ms
 Execution Time: 302.693 ms
(8 rows)

PG16 EXPLAIN output

                          QUERY PLAN
---------------------------------------------------------------
 GroupAggregate (actual rows=10 loops=1)
   Group Key: a
   Buffers: shared hit=892
   ->  Index Only Scan using aggtest_a_b_idx on aggtest (actual rows=1000000 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=892
 Planning Time: 0.061 ms
 Execution Time: 115.534 ms
(8 rows)

Помимо того, что PostgreSQL 16 выполняет запрос в два раза быстрее, чем в PG15, единственным признаком этого изменения в EXPLAIN ANALYZE является temp read=4540 written=4560, которого нет в PostgreSQL 16. В PG15 это вызвано тем, что неявная сортировка переносится на диск.

3. Разрешен memoize в UNION ALL (Richard Guo)

Enable use of Memoize atop an Append that came from UNION ALL

Узлы плана Memoize впервые были представлены в PostgreSQL 14. Memoize действует как слой кэша между параметризованным Nested Loop и внутренней частью вложенного цикла. Когда одно и то же значение нужно искать несколько раз, Memoize может дать хороший прирост производительности, поскольку он может пропустить выполнение своего подузла, если нужные строки уже были запрошены и находятся в кэше.

Планировщик запросов PostgreSQL 16 теперь будет учитывать использование Memoize, когда запрос UNION ALL появляется на внутренней части параметризованного Nested Loop.

-- Setup
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 (a INT PRIMARY KEY);
CREATE TABLE lookup (a INT);

INSERT INTO t1 SELECT x FROM generate_Series(1,10000) x;
INSERT INTO t2 SELECT x FROM generate_Series(1,10000) x;
INSERT INTO lookup SELECT x%10+1 FROM generate_Series(1,1000000)x;

ANALYZE t1,t2,lookup;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) t
INNER JOIN lookup l ON l.a = t.a;

PG15 EXPLAIN output

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop (actual rows=2000000 loops=1)
   ->  Seq Scan on lookup l (actual rows=1000000 loops=1)
   ->  Append (actual rows=2 loops=1000000)
         ->  Index Only Scan using t1_pkey on t1 (actual rows=1 loops=1000000)
               Index Cond: (a = l.a)
               Heap Fetches: 1000000
         ->  Index Only Scan using t2_pkey on t2 (actual rows=1 loops=1000000)
               Index Cond: (a = l.a)
               Heap Fetches: 1000000
 Planning Time: 0.223 ms
 Execution Time: 1926.151 ms
(11 rows)

PG16 EXPLAIN output

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Nested Loop (actual rows=2000000 loops=1)
   ->  Seq Scan on lookup l (actual rows=1000000 loops=1)
   ->  Memoize (actual rows=2 loops=1000000)
         Cache Key: l.a
         Cache Mode: logical
         Hits: 999990  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 2kB
         ->  Append (actual rows=2 loops=10)
               ->  Index Only Scan using t1_pkey on t1 (actual rows=1 loops=10)
                     Index Cond: (a = l.a)
                     Heap Fetches: 10
               ->  Index Only Scan using t2_pkey on t2 (actual rows=1 loops=10)
                     Index Cond: (a = l.a)
                     Heap Fetches: 10
 Planning Time: 0.229 ms
 Execution Time: 282.120 ms
(15 rows)

В PostgreSQL 16 EXPLAIN видно, что Memoize помещен поверх Append, что привело к сокращению количества loops в Append с 1 миллиона в PG15 до 10 в PG16. Каждый раз, когда Memoize попадает в кэш, нет необходимости выполнять Append для извлечения записей. Это приводит к тому, что запрос в PostgreSQL 16 выполняется примерно в 6 раз быстрее.

4. Разрешено выполнять anti-join с ненулевым входом в качестве внутреннего отношения (Richard Guo)

Support "Right Anti Join" plan shapes

При выполнении Hash Join для INNER JOIN PostgreSQL предпочитает создавать хэш-таблицу на основе меньшей из двух таблиц. Хэш-таблицы меньшего размера лучше, поскольку их создание требует меньше усилий. Меньшие по размеру таблицы также лучше использовать, поскольку они более cache-friendly для CPU, и меньше вероятность того, что CPU остановится в ожидании поступления данных из основной памяти.

До PostgreSQL 16, в Anti Join таблица, упомянутая в NOT EXISTS, всегда помещалась во внутреннюю часть объединения. Это означало, что не было возможности гибко хэшировать меньшую из двух таблиц, что, возможно, приводило к необходимости создания хэш-таблицы для таблицы большего размера.

Планировщик запросов PostgreSQL 16 может выбрать хэширование меньшей из двух таблиц. Теперь это можно сделать, поскольку PostgreSQL 16 поддерживает Right Anti Join.

-- Setup
CREATE TABLE small(a int);
CREATE TABLE large(a int);
INSERT INTO small
SELECT a FROM generate_series(1,100) a;
INSERT INTO large
SELECT a FROM generate_series(1,1000000) a;
VACUUM ANALYZE small,large;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM small s
WHERE NOT EXISTS(SELECT 1 FROM large l WHERE s.a = l.a);

PG15 EXPLAIN output

                          QUERY PLAN
---------------------------------------------------------------
 Hash Anti Join (actual rows=0 loops=1)
   Hash Cond: (s.a = l.a)
   ->  Seq Scan on small s (actual rows=100 loops=1)
   ->  Hash (actual rows=1000000 loops=1)
         Buckets: 262144  Batches: 8  Memory Usage: 6446kB
         ->  Seq Scan on large l (actual rows=1000000 loops=1)
 Planning Time: 0.103 ms
 Execution Time: 139.023 ms
(8 rows)

PG16 EXPLAIN output

                        QUERY PLAN
-----------------------------------------------------------
 Hash Right Anti Join (actual rows=0 loops=1)
   Hash Cond: (l.a = s.a)
   ->  Seq Scan on large l (actual rows=1000000 loops=1)
   ->  Hash (actual rows=100 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 12kB
         ->  Seq Scan on small s (actual rows=100 loops=1)
 Planning Time: 0.094 ms
 Execution Time: 77.076 ms
(8 rows)

Из-за того, что планировщик PG16 решил использовать Hash Right Anti Join, Memory Usage в PostgreSQL 16 намного меньше, чем в PostgreSQL 15, а Execution Time уменьшен почти вдвое.

5. Разрешено распараллеливание FULL и RIGHT OUTER хэш-соединения (Melanie Plageman, Thomas Munro)

Parallel Hash Full Join

В PostgreSQL 11 появилась Parallel Hash Join. Это позволяет нескольким параллельным обработчикам в параллельном запросе помогать в создании единой хэш-таблицы. В версиях до 11 каждый обработчик создавал свою собственную идентичную хэш-таблицу, что приводило к дополнительным затратам памяти.

В PostgreSQL 16 был улучшен Parallel Hash Join, теперь он поддерживает FULL и RIGHT типы соединений. Это позволяет выполнять параллельно запросы, у которых есть FULL OUTER JOIN, а также параллельно выполнять планы Right Joins.

-- Setup
CREATE TABLE odd (a INT);
CREATE TABLE even (a INT);
INSERT INTO odd
SELECT a FROM generate_series(1,1000000,2) a;
INSERT INTO even
SELECT a FROM generate_series(2,1000000,2) a;
VACUUM ANALYZE odd, even;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT COUNT(o.a),COUNT(e.a) FROM odd o FULL JOIN even e ON o.a = e.a;

PG15 EXPLAIN output

                            QUERY PLAN
-------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Hash Full Join (actual rows=1000000 loops=1)
         Hash Cond: (o.a = e.a)
         ->  Seq Scan on odd o (actual rows=500000 loops=1)
         ->  Hash (actual rows=500000 loops=1)
               Buckets: 262144  Batches: 4  Memory Usage: 6439kB
               ->  Seq Scan on even e (actual rows=500000 loops=1)
 Planning Time: 0.079 ms
 Execution Time: 220.677 ms
(9 rows)

PG16 EXPLAIN output

                                    QUERY PLAN
--------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual rows=1 loops=2)
               ->  Parallel Hash Full Join (actual rows=500000 loops=2)
                     Hash Cond: (o.a = e.a)
                     ->  Parallel Seq Scan on odd o (actual rows=250000 loops=2)
                     ->  Parallel Hash (actual rows=250000 loops=2)
                           Buckets: 262144  Batches: 4  Memory Usage: 6976kB
                           ->  Parallel Seq Scan on even e (actual rows=250000 loops=2)
 Planning Time: 0.161 ms
 Execution Time: 129.769 ms
(13 rows)

PostgreSQL 16 смог выполнить объединение параллельно, что привело к значительному сокращению Execution Time.

6. Разрешено оконным функциям использовать более быстрый режим ROWS, когда режим RANGE активен, но не нужен (David Rowley)

Allow window functions to adjust their frameOptions

Когда запрос содержит оконную функцию, такую как row_number(), rank(), dense_rank(), percent_rank() cume_dist(), ntile() и, если в условии не указан параметр ROWS, то PostgreSQL всегда будет использовать параметр RANGE по умолчанию.

Опция RANGE заставляет исполнителя заглядывать вперед, пока он не найдет первую “non-peer” строку. Peer строка - это строка в оконном фрейме, которая сравнивается одинаково в соответствии с ORDER BY в оконном выражении. Если ORDER BY отсутствует, все строки в оконном фрейме являются одноранговыми. При обработке записей, содержащих множество строк, которые сортируются одинаково в соответствии с ORDER BY в окне, дополнительная обработка для определения этих одноранговых строк может оказаться дорогостоящей.

Упомянутые выше оконные функции работают без изменений, независимо от того, указано ли ROWS или RANGE. Однако исполнитель(executor) в версиях PostgreSQL до 16 этого не знал, и поскольку для некоторых оконных функций действительно важна опция ROWS/RANGE, исполнителю приходилось выполнять проверки одноранговых строк во всех случаях.

Планировщик запросов PostgreSQL 16 знает, какие оконные функции нуждаются в опции ROWS/RANGE, и передает эту информацию исполнителю, чтобы тот мог пропустить ненужную дополнительную обработку.

Эта оптимизация работает особенно хорошо, когда row_number() используется для ограничения количества результатов в запросе, как показано в примере ниже.

-- Setup
CREATE TABLE scores (id INT PRIMARY KEY, score INT);
INSERT INTO scores SELECT s,random()*10 FROM generate_series(1,1000000)s;
CREATE INDEX ON scores(score);
VACUUM ANALYZE scores;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM (
    SELECT id,ROW_NUMBER() OVER (ORDER BY score) rn,score
    FROM scores
) m WHERE rn <= 10;

PG15 EXPLAIN output

                                 QUERY PLAN
-------------------------------------------------------------------------------
 WindowAgg (actual rows=10 loops=1)
   Run Condition: (row_number() OVER (?) <= 10)
   ->  Index Scan using scores_score_idx on scores (actual rows=50410 loops=1)
 Planning Time: 0.096 ms
 Execution Time: 29.775 ms
(5 rows)

PG16 EXPLAIN output

                                 QUERY PLAN
----------------------------------------------------------------------------
 WindowAgg (actual rows=10 loops=1)
   Run Condition: (row_number() OVER (?) <= 10)
   ->  Index Scan using scores_score_idx on scores (actual rows=11 loops=1)
 Planning Time: 0.191 ms
 Execution Time: 0.058 ms
(5 rows)

Index Scan в PG15 показывает, что 50410 строк должны были быть прочитаны из scores_score_idx индекса перед остановкой выполнения. В PostgreSQL 16 было прочитано только 11 строк, поскольку исполнитель понял, что как только row_number достигнет 11, больше не будет строк, соответствующих <= 10 условию. На PostgreSQL 16 этот запрос выполняется более чем в 500 раз быстрее.

7. Оптимизация постоянно увеличивающихся оконных функций ntile(), cume_dist() и percent_rank() (David Rowley)

Teach planner about more monotonic window functions

В PG15 планировщик запросов был изменен, чтобы позволить исполнителю(executor) досрочно прекращать обработку WindowAgg узлов. Это можно сделать, когда элемент в WHERE фильтрует оконную функцию таким образом, что, как только условие становится ложным, оно больше никогда не будет истинным.

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

Планировщик запросов PostgreSQL 16 расширяет область применения этой оптимизации, включив также ntile(), cume_dist() и percent_rank(). В PostgreSQL 15 это работало только для row_number(), rank(), dense_rank() count() и count(*).

-- Setup
CREATE TABLE marathon (id INT PRIMARY KEY, time INTERVAL NOT NULL);
INSERT INTO marathon
SELECT id,'03:00:00'::interval + (CAST(RANDOM() * 3600 AS INT) || 'secs')::INTERVAL - (CAST(RANDOM() * 3600 AS INT) || ' secs')::INTERVAL
FROM generate_series(1,50000) id;
CREATE INDEX ON marathon (time);
VACUUM ANALYZE marathon;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM (SELECT *,percent_rank() OVER (ORDER BY time) pr
FROM marathon) m WHERE pr <= 0.01;

PG15 EXPLAIN output

                              QUERY PLAN
-----------------------------------------------------------------------
 Subquery Scan on m (actual rows=500 loops=1)
   Filter: (m.pr <= '0.01'::double precision)
   Rows Removed by Filter: 49500
   ->  WindowAgg (actual rows=50000 loops=1)
         ->  Index Scan using marathon_time_idx on marathon (actual rows=50000 loops=1)
 Planning Time: 0.108 ms
 Execution Time: 84.358 ms
(7 rows)

PG16 EXPLAIN output

                              QUERY PLAN
-----------------------------------------------------------------------
 WindowAgg (actual rows=500 loops=1)
   Run Condition: (percent_rank() OVER (?) <= '0.01'::double precision)
   ->  Index Scan using marathon_time_idx on marathon (actual rows=50000 loops=1)
 Planning Time: 0.180 ms
 Execution Time: 19.454 ms
(5 rows)

Из вывода PostgreSQL 16 EXPLAIN видно, что планировщик смог использовать условие pr <= 0.01 как Run Condition, тогда как в PostgreSQL 15 это предложение появилось как Filter в подзапросе. В PG16 условие выполнения использовалось для досрочного прерывания выполнения WindowAgg узла. В результате Execution Time в PG16 оказался более чем в 4 раза быстрее, чем в PG15.

8. Разрешено удаление left join и unique joins в секционированных таблицах (Arne Roland)

Allow left join removals and unique joins on partitioned tables

Уже долгое время PostgreSQL может удалять LEFT JOIN, когда в запросе не требовалось ни одного столбца из объединенной слева таблицы, и объединение не могло дублировать ни одной строки.

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

Планировщик запросов PostgreSQL 16 теперь позволяет оптимизировать удаление LEFT JOIN в секционированных таблицах.

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

-- Setup
CREATE TABLE part_tab (id BIGINT PRIMARY KEY, payload TEXT) PARTITION BY HASH(id);
CREATE TABLE part_tab_p0 PARTITION OF part_tab FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE part_tab_p1 PARTITION OF part_tab FOR VALUES WITH (modulus 2, remainder 1);
CREATE TABLE normal_table (id INT, part_tab_id BIGINT);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT nt.* FROM normal_table nt LEFT JOIN part_tab pt ON nt.part_tab_id = pt.id;

PG15 EXPLAIN output

                            QUERY PLAN
-------------------------------------------------------------------
 Merge Right Join (actual rows=0 loops=1)
   Merge Cond: (pt.id = nt.part_tab_id)
   ->  Merge Append (actual rows=0 loops=1)
         Sort Key: pt.id
         ->  Index Only Scan using part_tab_p0_pkey on part_tab_p0 pt_1 (actual rows=0 loops=1)
               Heap Fetches: 0
         ->  Index Only Scan using part_tab_p1_pkey on part_tab_p1 pt_2 (actual rows=0 loops=1)
               Heap Fetches: 0
   ->  Sort (actual rows=0 loops=1)
         Sort Key: nt.part_tab_id
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on normal_table nt (actual rows=0 loops=1)
 Planning Time: 0.325 ms
 Execution Time: 0.037 ms
(14 rows)

PG16 EXPLAIN output

                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on normal_table nt (actual rows=0 loops=1)
 Planning Time: 0.244 ms
 Execution Time: 0.015 ms
(3 rows)

Важно отметить, что план PostgreSQL 16 не включает присоединение к part_tab, а значит, все, что нужно сделать, - это просканировать normal_table.

9. Использовать Limit вместо Unique для реализации DISTINCT, когда это возможно (David Rowley)

Use Limit instead of Unique to implement DISTINCT, when possible

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

-- Setup
CREATE TABLE abc (a int, b int, c int);
INSERT INTO abc SELECT a%10,a%10,a%10 FROM generate_series(1,1000000)a;
VACUUM ANALYZE abc;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT DISTINCT a,b,c FROM abc WHERE a = 5 AND b = 5 AND c = 5;

PG15 EXPLAIN output

                               QUERY PLAN
------------------------------------------------------------------------
 Unique (actual rows=1 loops=1)
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Unique (actual rows=1 loops=3)
               ->  Parallel Seq Scan on abc (actual rows=33333 loops=3)
                     Filter: ((a = 5) AND (b = 5) AND (c = 5))
                     Rows Removed by Filter: 300000
 Planning Time: 0.114 ms
 Execution Time: 30.381 ms
(10 rows)

PG16 EXPLAIN output

                    QUERY PLAN
---------------------------------------------------
 Limit (actual rows=1 loops=1)
   ->  Seq Scan on abc (actual rows=1 loops=1)
         Filter: ((a = 5) AND (b = 5) AND (c = 5))
         Rows Removed by Filter: 4
 Planning Time: 0.109 ms
 Execution Time: 0.025 ms
(6 rows)

Если вы внимательно посмотрите на SQL-запрос, то заметите, что каждый столбец в DISTINCT также содержит условие равенства в WHERE. Это означает, что все выходные строки в запросе будут иметь одинаковые значения в каждом столбце. Планировщик запросов PostgreSQL 16 может воспользоваться преимуществами этих знаний и просто с помощью LIMIT преобразовывать результаты запроса в 1 строку. PostgreSQL 15 выдал тот же результат запроса, прочитав все результаты целиком и используя оператор Unique, чтобы свести все строки к одной строке. Execution Time для PostgreSQL 16 был более чем в 1200 раз быстрее, чем для PostgreSQL 15.

10. Ослаблены слишком строгие правила в select_outer_pathkeys_for_merge() (David Rowley)

Relax overly strict rules in select_outer_pathkeys_for_merge()

До появления PostgreSQL 16, когда планировщик запросов рассматривал возможность выполнения Merge Join, он проверял, соответствует ли порядок сортировки слияния какой-либо операции плана верхнего уровня (такой как DISTINCT, GROUP BY или ORDER BY), и использовал этот порядок только в том случае, если он точно соответствовал требованиям для верхнего уровня. Этот вариант был немного устаревшим, поскольку для этих операций верхнего уровня можно использовать Incremental Sorts, а инкрементальные сортировки могут использовать преимущества результатов, которые предварительно отсортированы только по некоторым ведущим столбцам, по которым нужно отсортировать результаты.

Планировщик запросов PostgreSQL 16 изменил правило, используемое при рассмотрении порядка Merge Join, с “порядок строк должен точно совпадать” на “должен быть хотя бы 1 ведущий столбец, правильно упорядоченный". Это позволяет планировщику использовать Incremental Sorts для приведения строк в правильный порядок для выполнения операций верхнего уровня. Ранее в этом блоге мы узнали, что инкрементальная сортировка, когда она возможна, требует меньше работы, чем полная сортировка, поскольку инкрементальная сортировка может использовать частично отсортированные входные данные и выполнять сортировку меньшими частями, что приводит к меньшему потреблению памяти и меньшему количеству сравнений в целом.

-- Setup

CREATE TABLE a (a INT, b INT);
CREATE TABLE b (x INT, y INT);
INSERT INTO a SELECT a,a FROM generate_series(1,1000000) a;
INSERT INTO b SELECT a,a FROM generate_series(1,1000000) a;
VACUUM ANALYZE a, b;

SET enable_hashjoin=0;
SET max_parallel_workers_per_gather=0;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT a,b,count(*) FROM a INNER JOIN b ON a.a = b.x GROUP BY a,b ORDER BY a DESC, b;

PG15 EXPLAIN output

                                QUERY PLAN
---------------------------------------------------------------------------
 GroupAggregate (actual rows=1000000 loops=1)
   Group Key: a.a, a.b
   ->  Sort (actual rows=1000000 loops=1)
         Sort Key: a.a DESC, a.b
         Sort Method: external merge  Disk: 17664kB
         ->  Merge Join (actual rows=1000000 loops=1)
               Merge Cond: (a.a = b.x)
               ->  Sort (actual rows=1000000 loops=1)
                     Sort Key: a.a
                     Sort Method: external merge  Disk: 17664kB
                     ->  Seq Scan on a (actual rows=1000000 loops=1)
               ->  Materialize (actual rows=1000000 loops=1)
                     ->  Sort (actual rows=1000000 loops=1)
                           Sort Key: b.x
                           Sort Method: external merge  Disk: 11768kB
                           ->  Seq Scan on b (actual rows=1000000 loops=1)
 Planning Time: 0.175 ms
 Execution Time: 1010.738 ms
(18 rows)

PG16 EXPLAIN output

                                QUERY PLAN
---------------------------------------------------------------------------
 GroupAggregate (actual rows=1000000 loops=1)
   Group Key: a.a, a.b
   ->  Incremental Sort (actual rows=1000000 loops=1)
         Sort Key: a.a DESC, a.b
         Presorted Key: a.a
         Full-sort Groups: 31250  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
         ->  Merge Join (actual rows=1000000 loops=1)
               Merge Cond: (a.a = b.x)
               ->  Sort (actual rows=1000000 loops=1)
                     Sort Key: a.a DESC
                     Sort Method: external merge  Disk: 17672kB
                     ->  Seq Scan on a (actual rows=1000000 loops=1)
               ->  Materialize (actual rows=1000000 loops=1)
                     ->  Sort (actual rows=1000000 loops=1)
                           Sort Key: b.x DESC
                           Sort Method: external merge  Disk: 11768kB
                           ->  Seq Scan on b (actual rows=1000000 loops=1)
 Planning Time: 0.140 ms
 Execution Time: 915.589 ms
(19 rows)

В PG16 EXPLAIN вы можете видеть, что был использован Incremental Sort (по сравнению с PG15, в котором вместо этого использовался Sort), и это привело к небольшому сокращению Execution Time в PG16 и значительному сокращению объема памяти, используемой для выполнения сортировки.

Заключение

В PostgreSQL 16 была проделана большая инженерная работа по улучшению планировщика запросов многими инженерами со всего мира.

Каждое из 10 улучшений в планировщике PostgreSQL 16, описанных выше, включено по умолчанию - и либо применяется во всех случаях, когда возможна оптимизация, либо применяется выборочно планировщиком запросов, когда он считает, что оптимизация поможет.

Если вы используете более старую версию PostgreSQL, я рекомендую вам попробовать свою рабочую нагрузку на PostgreSQL 16, чтобы увидеть, какие из ваших запросов выполняются быстрее. И, как всегда, отзывы о реальном использовании PostgreSQL приветствуются в pgsql-general@postgresql.org списке рассылки — не обязательно писать только о проблемах, вы всегда можете поделиться и положительным опытом.

Что нового в планировщике / оптимизаторе запросов Postgres 16