Добрый день, коллеги! Как известно, компания "Postgres Professional" более не рекомендует использовать секционирование с использованием pg_pathman, а рекомендует использовать вместо него декларативное секционирование. Однако помимо синтаксического сахара и утилитарного кода по поддержке интервального (в стиле Oracle) секционирования, я обнаружил для себя ещё одну проблему в такой миграции (переходе). Надеюсь, коллеги из PGPro доработают декларативное секционирование к моменту окончательного выпиливания pg_pathman.
Эксперименты проводились на PostgresPro Ent 15.3 c расширением pg_pathman от того же pgPro.
Как известно, partition pruning является одной из самых востребованных техник по оптимизации производительности для секционированных таблиц. В более или менее сложных системах вряд ли удастся обойтись без так называемого "dynamic partition pruning", т.е. ситуаций, когда секции, которые можно откинуть, известны только в runtime (момент исполнения).
Давайте посмотрим, как обстоят дела с "dynamic partition pruning" у декларативного и pg_pathman секционирования.
Проведем тест:
для начала создадим 2 секционированные таблички: одна использует декларативное секционирование, вторая секционирование при помощи pg_pathman.
Табличка buh_operations секционирована по RANGE с использованием декларативного секционирования.
drop table if exists test_part.buh_operations cascade;
create table test_part.buh_operations
(
op_id INT8,
op_date DATE,
op_code VARCHAR(20),
op_money numeric
) PARTITION BY RANGE (op_date);
--- добавляем 300 секций
do $$
declare
r record;
begin
for r in select
format ('CREATE TABLE test_part.buh_operations_%s PARTITION OF buh_operations FOR VALUES FROM (DATE''%s'') TO (DATE''%s'')'
,to_char(bgn, 'YYYYMMDD'), to_char(bgn, 'YYYY-MM-DD'), to_char(fnsh, 'YYYY-MM-DD')) as stmt
from (
select '2024-08-16'::date - g as bgn, '2024-08-16'::date - g +1 as fnsh from pg_catalog.generate_series (1,300) g
) b loop
raise notice '%', r.stmt ;
execute r.stmt;
end loop;
end;
$$
-- заполняем тестовыми данными
insert into test_part.buh_operations
select g, '2024-08-16'::date - (g/1000)::int , 'INC28', random()*100+50 from pg_catalog.generate_series(1000, 300000) g;
Табличка buh_operations_pth секционирована по RANGE с использованием pg_pathman секционирования.
drop table if exists test_part.buh_operations_pth CASCADE;
create table test_part.buh_operations_pth
(
op_id INT8,
op_date DATE not NULL,
op_code VARCHAR(20),
op_money numeric
)
;
insert into test_part.buh_operations_pth values (0, '2024-01-01'::date, 'INC34', 0.0 ); -- ALTER Не работает на пустой таблице
ALTER TABLE test_part.buh_operations_pth PARTITION BY RANGE (op_date) START FROM ('2023-10-21'::date) INTERVAL (interval'1 day');
delete from test_part.buh_operations_pth; --чистим ненужные данные
-- заполняем тестовыми данными
insert into test_part.buh_operations_pth
select g, '2024-08-16'::date - (g/1000)::int , 'INC28', random()*100+50 from pg_catalog.generate_series(1000, 300000) g;
В результате получим 2 таблички по ~300 секций в каждой. Получается, что почти в каждой секции по 1000 записей.
Простейший "dynamic partition pruning"
Попробуем вариант с одной табличкой и динамическим параметром, но для начала отключим параллельное исполнение для простоты эксперимента (на основные результаты это не повлияет). Итак:
set max_parallel_workers_per_gather = 0;
Случай "декларативного" секционирования
explain analyze select * from test_part.buh_operations where op_date in (select now()::date - 10);
Append (cost=0.00..8380.74 rows=1299 width=29) (actual time=0.235..1.304 rows=1000 loops=1) |
Subplans Removed: 299 |
-> Seq Scan on buh_operations_20240805 buh_operations_1 (cost=0.00..28.00 rows=1000 width=29) (actual time=0.233..1.058 rows=1000 loops=1)|
Filter: (op_date = ((now())::date - 10)) |
Planning Time: 7.054 ms |
Execution Time: 1.478 ms |Видно, что сработал "dynamic partition pruning". Мы просканировали 1 секцию из 300.
Случай pg_pathman секционирования
explain analyze select * from test_part.buh_operations_pth bo where op_date in (select now()::date -10);
Append (cost=0.00..8423.32 rows=1304 width=29) (actual time=69.623..72.055 rows=1000 loops=1) |
-> Seq Scan on buh_operations_pth_inf bo_1 (cost=0.00..22.40 rows=3 width=102) (actual time=0.010..0.010 rows=0 loops=1)|
Filter: (op_date = ((now())::date - 10)) |
-> Seq Scan on buh_operations_pth_1 bo_2 (cost=0.00..22.40 rows=3 width=102) (actual time=0.013..0.013 rows=0 loops=1) |
Filter: (op_date = ((now())::date - 10)) |
Rows Removed by Filter: 1 |
-> Seq Scan on buh_operations_pth_2 bo_3 (cost=0.00..28.00 rows=1 width=29) (actual time=0.203..0.203 rows=0 loops=1) |
Filter: (op_date = ((now())::date - 10)) |
Rows Removed by Filter: 1000
.......................................
-> Seq Scan on buh_operations_pth_299 bo_300 (cost=0.00..28.00 rows=1 width=29) (actual time=0.203..0.203 rows=0 loops=1) |
Filter: (op_date = ((now())::date - 10)) |
Rows Removed by Filter: 1000 |
-> Seq Scan on buh_operations_pth_300 bo_301 (cost=0.00..28.00 rows=1 width=29) (actual time=0.206..0.206 rows=0 loops=1) |
Filter: (op_date = ((now())::date - 10)) |
Rows Removed by Filter: 1000 |
Planning Time: 8.325 ms |
Execution Time: 63.628 ms | |Видно, что partition pruning не сработал для секционирования через pg_pathman.Мы видим, что происходили реальные сканирования всех секций в runtime, которые затем откидывались фильтрами (Rows Removed by Filter: 1000).
Пока декларативное секционирование выглядит лучше pg_pathman, но давайте рассмотрим более реалистичный случай.
Более сложный пример (join с несекционированной таблицей)
Создадим маленькую несекционированную табличку:
drop table if exists test_part.simple_dict;
create table test_part.simple_dict(op_code VARCHAR(20),op_date DATE,op_descript VARCHAR(2000));
И заполним её данными. Таблица будет содержать даты только с 2024-08-01:
insert into test_part.simple_dict (op_code, op_date, op_descript)
select 'INC28' as op_code, '2024-08-16'::date - g::int as op_date, 'assadfsdsdgYYTRYTZXXZXXbmbbmb' as op_descript
from pg_catalog.generate_series(1,15) g;Join для декларативно-секционированной таблицы
Посмотрим на джойн секционированной таблички: с простой логикой только 15 секций должны участвовать в выборке.
Hash Join (cost=13.25..9144.86 rows=972 width=545) (actual time=75.538..81.900 rows=15000 loops=1) |
Hash Cond: ((a.op_date = b.op_date) AND ((a.op_code)::text = (b.op_code)::text)) |
-> Append (cost=0.00..6878.93 rows=299062 width=29) (actual time=0.022..42.555 rows=299001 loops=1) |
-> Seq Scan on buh_operations_20231021 a_1 (cost=0.00..1.62 rows=62 width=102) (actual time=0.020..0.021 rows=1 loops=1) |
-> Seq Scan on buh_operations_20231022 a_2 (cost=0.00..18.00 rows=1000 width=29) (actual time=0.007..0.087 rows=1000 loops=1) |
-> Seq Scan on buh_operations_20231023 a_3 (cost=0.00..18.00 rows=1000 width=29) (actual time=0.005..0.088 rows=1000 loops=1) |
............................. много строк ............................
-> Seq Scan on buh_operations_20240814 a_299 (cost=0.00..18.00 rows=1000 width=29) (actual time=0.006..0.082 rows=1000 loops=1)|
-> Seq Scan on buh_operations_20240815 a_300 (cost=0.00..18.00 rows=1000 width=29) (actual time=0.006..0.084 rows=1000 loops=1)|
-> Hash (cost=11.30..11.30 rows=130 width=578) (actual time=0.018..0.018 rows=15 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 10kB |
-> Seq Scan on simple_dict b (cost=0.00..11.30 rows=130 width=578) (actual time=0.007..0.010 rows=15 loops=1) |
Planning Time: 4.384 ms |
Execution Time: 83.242 ms |
Видно, что partition pruning не произошёл, мы просканировали все 300 секций и передали целиком все 1000 записей на следующий шаг выполнения execution plan'a.
Join для pg_pathman-секционированной таблицы
Nested Loop (cost=0.00..4294.82 rows=976 width=618) (actual time=0.203..9.523 rows=15000 loops=1) |
-> Seq Scan on simple_dict b (cost=0.00..11.30 rows=130 width=578) (actual time=0.159..0.163 rows=15 loops=1) |
-> Custom Scan (RuntimeAppend) (cost=0.00..22.98 rows=997 width=29) (actual time=0.010..0.474 rows=1000 loops=15) |
Prune by: (b.op_date = a.op_date) |
-> Seq Scan on buh_operations_pth_300 a_15 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.016..0.192 rows=1000 loops=1)|
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_299 a_14 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.007..0.178 rows=1000 loops=1)|
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_298 a_13 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.012..0.183 rows=1000 loops=1)|
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_297 a_12 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.005..0.406 rows=1000 loops=1)|
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_296 a_11 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.016..0.291 rows=1000 loops=1)|
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_295 a_10 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.010..0.250 rows=1000 loops=1)|
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_294 a_9 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.008..0.241 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_293 a_8 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.009..0.300 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_292 a_7 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.009..0.240 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_291 a_6 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.010..0.243 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_290 a_5 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.008..0.244 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_289 a_4 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.011..0.248 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_288 a_3 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.009..0.238 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_287 a_2 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.008..0.354 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
-> Seq Scan on buh_operations_pth_286 a_1 (cost=0.00..23.00 rows=1000 width=29) (actual time=0.009..0.250 rows=1000 loops=1) |
Filter: ((b.op_date = op_date) AND ((b.op_code)::text = (op_code)::text)) |
Planning Time: 9.022 ms |
Execution Time: 10.348 ms |Видно, что план переключился на Nested loop и произошёл "partition pruning".
Было просканировано, как и ожидалось только 15 секций из 300, а также см. на специфичный шаг плана - Custom Scan (RuntimeAppend) и фразу Prune by: (b.op_date = a.op_date)
Резюме
Хотя на первый взгляд может показаться что декларативное секционирование уже не хуже pg_pathman, но всё ещё существуют вполне реалистичные сценарии, когда pg_pathman более адекватен.