Комментарии 13
Отличная технология и давно напрашивалась. С какой версии PG можно будет ее протестировать на практике?
И еще большая просьба - сделать не только общую настройку для времени выполнения и ограничение по строкам для всех запросов на PG, но и возможность в приложении указывать комментарий с настройками только для определенных запросов SQL (не активировать технологию для других). Например,
/*replan
duration = 43 мс
limit_rows = 1000
*/
SELECT ...
FROM Table1 JOIN Table2 ... JOIN TableN
WHERE ...
А зачем перебираются связи всех таблиц со всеми при поиске плана? Часто таблицы связываются по цепочке, т.е. только некотрые таблицы могут быть связаны напрямую.
То, что предложили вы, может сработать для небольших по сложности запросов (если в них указано малое количество таблиц). Потому что связать их как есть бывает дорого. Для оптимизатора стоит цель сформировать такой план с таким порядком соединений, чтобы на уровне выше executor обрабатывал как можно меньшее количество строк, что можно для нод находящимся на уровне ниже. Кстати, это могут жать параметризованные ноды, так как из-за наличия условия соединения, что фильтруют туплы, у нас получается меньшее количество туплов на выходе для выполнения ноды выше. Чтобы найти правильный порядок соединений, оптимизатору нужно пересмотреть комбинацию таблиц для составления справедливых пар (или джойнов). Под словом справедливый я подразумеваю, что иногда невозможно составить джойн двух таблиц из-за сохранения логической эквивалентности.
К тому же, стоит учесть, что оптимизатор умеет переставлять местами inner join, но не может это сделать с outer join.
Вы можете посмотреть доклад из PGConf Canada 2024, чтобы понять подробнее, как оптимизатор работает, если интересно (с 12 слайда).
Не совмем понятно. Вот допустим есть 5 таблиц в запросе и есть среди них inner join со связью t1. Id = t2. Id. Какой тут смысл другие таблицы подставлять для оценки плана запроса? Связь однозначно говорит, что нужно брать таблицы t1 и t2 и максимум можно их местами в join-е поменять.
Добрый день, пока для 5 таблиц хороший пример, для демонстрации того, о чем говорилось выше я не успела сформировать, но нашла отличный пример из регрессионных тестов Postgres (ветка master).
Кстати, чтобы получить случай тот, что вы хотите - чтобы планнер строил план, как написано, достаточно поставить join_collapse_limit = 1, тогда Postgres построит план так, как вы указали в запросе без использования процедуры планирования, но я не советую это делать. Случаи, когда это может дать ускорение на мой взгляд редкие.
И так, для своего примера использовала таблицу и данные из Postgres, которые он использует для регрессионных тестов.
set
alena@postgres=# CREATE TABLE tenk1 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
); string4 name, int4,
CREATE TABLE
alena@postgres=# COPY tenk1 FROM '/home/alena/postgrespro5/src/test/regress/data/tenk.data';
COPY 10000
alena@postgres=# analyze;
ANALYZE
alena@postgres=# CREATE TABLE tenk2 AS SELECT * FROM tenk1;
SELECT 10000
alena@postgres=# analyze;
ANALYZE
alena@postgres=# set from_collapse_limit =8;
SET
alena@postgres=# explain analyze SELECT * FROM tenk1 A INNER JOIN tenk2 B
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd)
WHERE a.thousand < 750;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1165.50..10869.88 rows=750000 width=488) (actual time=14.177..310.983 rows=750000 loops=1)
Hash Cond: (c.odd = b.odd)
-> Hash Join (cost=556.50..1804.62 rows=7500 width=248) (actual time=6.379..12.507 rows=7500 loops=1)
Hash Cond: (a.hundred = c.hundred)
-> Seq Scan on tenk1 a (cost=0.00..470.00 rows=7500 width=244) (actual time=0.017..3.141 rows=7500 loops=1)
Filter: (thousand < 750)
Rows Removed by Filter: 2500
-> Hash (cost=544.00..544.00 rows=1000 width=8) (actual time=6.350..6.352 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> HashAggregate (cost=534.00..544.00 rows=1000 width=8) (actual time=6.303..6.325 rows=100 loops=1)
Group Key: c.odd, c.hundred
Batches: 1 Memory Usage: 73kB
-> Seq Scan on tenk2 c (cost=0.00..484.00 rows=10000 width=8) (actual time=0.006..3.127 rows=10000 loops=1)
-> Hash (cost=484.00..484.00 rows=10000 width=244) (actual time=7.773..7.774 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 2824kB
-> Seq Scan on tenk2 b (cost=0.00..484.00 rows=10000 width=244) (actual time=0.022..2.252 rows=10000 loops=1)
Planning Time: 1.074 ms
Execution Time: 330.138 ms
(18 rows)
alena@postgres=# set from_collapse_limit =1;
SET
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=559.00..2251531.75 rows=75000000 width=488) (actual time=18.092..41629.931 rows=750000 loops=1)
Hash Cond: ((b.odd = c.odd) AND (a.hundred = c.hundred))
-> Nested Loop (cost=0.00..938472.75 rows=75000000 width=488) (actual time=0.067..32202.781 rows=75000000 loops=1)
-> Seq Scan on tenk2 b (cost=0.00..484.00 rows=10000 width=244) (actual time=0.034..9.179 rows=10000 loops=1)
-> Materialize (cost=0.00..507.50 rows=7500 width=244) (actual time=0.000..0.438 rows=7500 loops=10000)
-> Seq Scan on tenk1 a (cost=0.00..470.00 rows=7500 width=244) (actual time=0.018..2.606 rows=7500 loops=1)
Filter: (thousand < 750)
Rows Removed by Filter: 2500
-> Hash (cost=544.00..544.00 rows=1000 width=8) (actual time=17.575..17.578 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> HashAggregate (cost=534.00..544.00 rows=1000 width=8) (actual time=17.471..17.518 rows=100 loops=1)
Group Key: c.odd, c.hundred
Batches: 1 Memory Usage: 73kB
-> Seq Scan on tenk2 c (cost=0.00..484.00 rows=10000 width=8) (actual time=0.010..8.377 rows=10000 loops=1)
Planning Time: 1.024 ms
Execution Time: 41661.372 ms
(16 rows)Обратите внимание, что если вы построете план, как есть (второй план), у вас для Nested Loop вместо 7500 туплов приходит на обработку 75000000, потому что а первом плане, вы фильтруете условием (a.hundred = c.hundred) и по сути у вас получаются все уникальные туплы из столбца а и на Hash Join вы обрабатываете меньшее количество туплов, чем во втором плане.
Другой пример, связанный с параметризацией, содержит в таблицах малое количество данных, потому не будет так очевиден по времени, но по количеству обрабатываемых туплов, можно сделать выводы:
alena@postgres=# create temp table nt1 (
id int primary key,
a1 boolean,
a2 boolean
);
create temp table nt2 (
id int primary key,
nt1_id int,
b1 boolean,
b2 boolean,
foreign key (nt1_id) references nt1(id)
);
create temp table nt3 (
id int primary key,
nt2_id int,
c1 boolean,
foreign key (nt2_id) references nt2(id)
);
insert into nt1 values (1,true,true);
insert into nt1 values (2,true,false);
insert into nt1 values (3,false,false);
where nt3.id = 1 and ss2.b3;_id is not null) as a3 from nt1) as ss1
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
alena@postgres=# set join_collapse_limit =8;
SET
alena@postgres=# explain analyze select nt3.id
from nt3 as nt3
left join
(select nt2.*, (nt2.b1 and ss1.a3) AS b3
from nt2 as nt2
left join
(select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
on ss1.id = nt2.nt1_id
) as ss2
on ss2.id = nt3.nt2_id
where nt3.id = 1 and ss2.b3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.46..16.56 rows=1 width=4) (actual time=0.087..0.092 rows=1 loops=1)
-> Nested Loop (cost=0.31..16.35 rows=1 width=9) (actual time=0.065..0.069 rows=1 loops=1)
-> Index Scan using nt3_pkey on nt3 (cost=0.15..8.17 rows=1 width=8) (actual time=0.045..0.048 rows=1 loops=1)
Index Cond: (id = 1)
-> Index Scan using nt2_pkey on nt2 (cost=0.15..8.17 rows=1 width=9) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (id = nt3.nt2_id)
-> Index Only Scan using nt1_pkey on nt1 (cost=0.15..0.19 rows=1 width=5) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: (id = nt2.nt1_id)
Filter: (nt2.b1 AND (id IS NOT NULL))
Heap Fetches: 1
Planning Time: 0.748 ms
Execution Time: 0.197 ms
(12 rows)
alena@postgres=# set join_collapse_limit =1;
SET
alena@postgres=# explain analyze select nt3.id
from nt3 as nt3
left join
(select nt2.*, (nt2.b1 and ss1.a3) AS b3
from nt2 as nt2
left join
(select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1
on ss1.id = nt2.nt1_id
) as ss2
on ss2.id = nt3.nt2_id
where nt3.id = 1 and ss2.b3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=72.19..112.01 rows=1 width=4) (actual time=0.127..0.139 rows=1 loops=1)
Hash Cond: (nt2.id = nt3.nt2_id)
-> Hash Join (cost=64.00..101.02 rows=1070 width=4) (actual time=0.050..0.059 rows=2 loops=1)
Hash Cond: (nt2.nt1_id = nt1.id)
Join Filter: (nt2.b1 AND ((nt1.id IS NOT NULL)))
Rows Removed by Join Filter: 1
-> Seq Scan on nt2 (cost=0.00..31.40 rows=2140 width=9) (actual time=0.014..0.016 rows=3 loops=1)
-> Hash (cost=34.00..34.00 rows=2400 width=5) (actual time=0.018..0.019 rows=3 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 33kB
-> Seq Scan on nt1 (cost=0.00..34.00 rows=2400 width=5) (actual time=0.010..0.013 rows=3 loops=1)
-> Hash (cost=8.17..8.17 rows=1 width=8) (actual time=0.065..0.066 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using nt3_pkey on nt3 (cost=0.15..8.17 rows=1 width=8) (actual time=0.054..0.058 rows=1 loops=1)
Index Cond: (id = 1)
Planning Time: 0.702 ms
Execution Time: 0.272 ms
(16 rows)Обратите внимание на условие Hash Cond: (nt2.id = nt3.nt2_id) во втором запросе (где план формируется практически, как мы написали), оно в запросе 2, когда мы дали возможность планнеру подумать над планом перенесло его в фильтр сканирования индекса в первом запросе:
-> Index Scan using nt2_pkey on nt2 (cost=0.15..8.17 rows=1 width=9) (actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (id = nt3.nt2_id)Это позволло перед выполнения Join ноды (Nested Loop) сократить немного объем данных. в данном примере на один тупл, конечно, но представьте себе профит, если увеличить размер таблицы, насколько сократиться время выполнения, потому что будет меньше объема данных обрабатываться.
Остальные, похожие примеры вы можете сами посмотреть, если добавить в конфигрурацию постреса join_collapse_limit=1, рестарт инстанса и запустите make installcheck-world. Конечно, у вас в регрессионных тестах будут отображены изменения планов, но думаю вы найдете дополнительные примеры поясняющие ответ на ваш вопрос. Если не очень понятно, могу скинуть код bash.
Спасибо за развернутый ответ, тянет на отдельную статью. Мы сейчас как раз боремся с планировщиком pg, при миграции с ms на pg запросы стали работать процентов на 30 медленнее. Пока для себя решили переписывать сложные запросы или разбивать на группу более простых. В том числе и по возможности убирать подзапросы аналогичные рассмотренному в первом примере.
Поэтому очень интересно, как оно там внутри устроено и на что можно повлиять.
Действительно, заинтриговали, но не сказали самого интересного: какой статус этой разработки? Экспериментальный патч в недрах Postgres Pro? Доработка для следующей версии Postgres Pro EE? Патч, отправленный в hackers?
Пока это разработка в рамках Postgres Pro Enterprise. Потом, вероятнее всего, будет и патч для ваниллы. Но такие тяжелые вещи туда быстро не принимают. Если вообще решат брать.
Он вышел из статуса “эксперементальныйй”, но не достиг вершины статуса своего развития) Можете найти его в Enterprise16
https://postgrespro.ru/docs/enterprise/16/realtime-query-replanning-how
Built-in replanning как способ корректировать огрехи оптимизатора PostgreSQL