Доброго дня. pg 12.5

Может кто то объяснить или ткнуть в доку

1 запрос
select cd.* from
form f
join form_version fv on fv.form_id = f.id
join cell_data cd on cd.form_version_from = fv.id
where f.template_id = 37201

план
Gather (cost=4591.86..3674000.89 rows=1511461 width=32)
Workers Planned: 4
-> Parallel Hash Join (cost=3591.86..3521854.79 rows=377865 width=32)
Hash Cond: (cd.form_version_from = fv.id)
-> Parallel Seq Scan on cell_data cd (cost=0.00..2729816.06 rows=209927406 width=32)
-> Parallel Hash (cost=3549.30..3549.30 rows=3405 width=4)
-> Nested Loop (cost=82.91..3549.30 rows=3405 width=4)
-> Parallel Bitmap Heap Scan on form f (cost=82.48..1104.44 rows=3255 width=4)
Recheck Cond: (template_id = 37201)
-> Bitmap Index Scan on form_id_templ_idx (cost=0.00..79.96 rows=10090 width=0)
Index Cond: (template_id = 37201)
-> Index Scan using form_version_form_id_idx on form_version fv (cost=0.43..0.73 rows=2 width=8)
Index Cond: (form_id = f.id)




2 запрос
select cd.* from cell_data cd
where cd.form_version_from = any (array(
select fv.id from
form f
join form_version fv on fv.form_id = f.id
where f.template_id = 37201
))

план
Index Scan using cell_data_form_version_from_idx on cell_data cd (cost=5605.38..43005.43 rows=305028 width=32)
Index Cond: (form_version_from = ANY ($2))
InitPlan 1 (returns $2)
-> Gather (cost=1082.91..5604.80 rows=10555 width=4)
Workers Planned: 3
-> Nested Loop (cost=82.91..3549.30 rows=3405 width=4)
-> Parallel Bitmap Heap Scan on form f (cost=82.48..1104.44 rows=3255 width=4)
Recheck Cond: (template_id = 37201)
-> Bitmap Index Scan on form_id_templ_idx (cost=0.00..79.96 rows=10090 width=0)
Index Cond: (template_id = 37201)
-> Index Scan using form_version_form_id_idx on form_version fv (cost=0.43..0.73 rows=2 width=8)
Index Cond: (form_id = f.id)


В первом запросе seq scan form_version
Во втором index scan по fv.form_id

Вообще какой механизм\принцип работы оптимизатора в данных случаях не очень понял. Да во втором случае в плане =any($2), но константа это массив, вроде бы ему надо по каждому элементу массива найти запись в form_version. Что можно предпринимать в таких случаях, чтоб направить запрос в индекс?

про индексы не понял вопроса, они же в плане есть 2го запроса


1 запрос
Gather (cost=4592.71..3682545.31 rows=1514989 width=32) (actual time=18223.673..255031.426 rows=1438658 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=117767 read=6240840 dirtied=25
-> Parallel Hash Join (cost=3592.71..3530046.41 rows=378747 width=32) (actual time=18196.286..254861.298 rows=287732 loops=5)
Hash Cond: (cd.form_version_from = fv.id)
Buffers: shared hit=117767 read=6240840 dirtied=25
-> Parallel Seq Scan on cell_data cd (cost=0.00..2736171.50 rows=210416150 width=32) (actual time=0.261..235621.765 rows=168011932 loops=5)
Buffers: shared hit=79263 read=6240837 dirtied=25
-> Parallel Hash (cost=3549.97..3549.97 rows=3419 width=4) (actual time=27.968..27.972 rows=2085 loops=5)
Buckets: 16384 Batches: 1 Memory Usage: 608kB
Buffers: shared hit=38297 read=3
-> Nested Loop (cost=82.91..3549.97 rows=3419 width=4) (actual time=0.353..27.107 rows=2085 loops=5)
Buffers: shared hit=38297 read=3
-> Parallel Bitmap Heap Scan on form f (cost=82.48..1104.44 rows=3255 width=4) (actual time=0.263..3.916 rows=1836 loops=5)
Recheck Cond: (template_id = 37201)
Heap Blocks: exact=350
Buffers: shared hit=1061
-> Bitmap Index Scan on form_id_templ_idx (cost=0.00..79.96 rows=10090 width=0) (actual time=0.954..0.955 rows=9182 loops=1)
Index Cond: (template_id = 37201)
Buffers: shared hit=29
-> Index Scan using form_version_form_id_idx on form_version fv (cost=0.43..0.73 rows=2 width=8) (actual time=0.011..0.012 rows=1 loops=9182)
Index Cond: (form_id = f.id)
Buffers: shared hit=37236 read=3
Planning Time: 4.160 ms
Execution Time: 255100.478 ms


2. запрос
Index Scan using cell_data_form_version_from_idx on cell_data cd (cost=5610.54..43095.65 rows=305724 width=32) (actual time=37.804..45285.326 rows=1438658 loops=1)
Index Cond: (form_version_from = ANY ($2))
Buffers: shared hit=82368 read=18627 dirtied=56
InitPlan 1 (returns $2)
-> Gather (cost=1082.91..5609.97 rows=10600 width=4) (actual time=2.049..31.851 rows=10425 loops=1)
Workers Planned: 3
Workers Launched: 3
Buffers: shared hit=38299
-> Nested Loop (cost=82.91..3549.97 rows=3419 width=4) (actual time=0.508..26.195 rows=2606 loops=4)
Buffers: shared hit=38299
-> Parallel Bitmap Heap Scan on form f (cost=82.48..1104.44 rows=3255 width=4) (actual time=0.408..5.135 rows=2296 loops=4)
Recheck Cond: (template_id = 37201)
Heap Blocks: exact=283
Buffers: shared hit=1061
-> Bitmap Index Scan on form_id_templ_idx (cost=0.00..79.96 rows=10090 width=0) (actual time=1.265..1.266 rows=9182 loops=1)
Index Cond: (template_id = 37201)
Buffers: shared hit=29
-> Index Scan using form_version_form_id_idx on form_version fv (cost=0.43..0.73 rows=2 width=8) (actual time=0.008..0.009 rows=1 loops=9182)
Index Cond: (form_id = f.id)
Buffers: shared hit=37238
Planning Time: 4.459 ms
Execution Time: 45367.751 ms

Maxim Boguk,

Параметры оптимизатора были экспериментально выведены на пг 9.5 и потом в таком же виде мигрировали на 12.5 версию
seq_page_cost = 0.1
random_page_cost = 0.11

ваш запрос
Gather (cost=4592.93..3450201.38 rows=1515557 width=32) (actual time=1481.243..253496.767 rows=1438699 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=126889 read=6234277 dirtied=46
-> Parallel Hash Semi Join (cost=3592.93..3297645.68 rows=378889 width=32) (actual time=1492.398..253269.593 rows=287740 loops=5)
Hash Cond: (cd.form_version_from = fv.id)
Buffers: shared hit=126889 read=6234277 dirtied=46
-> Parallel Seq Scan on cell_data cd (cost=0.00..2737272.88 rows=210500848 width=32) (actual time=0.058..236090.025 rows=168077706 loops=5)
Buffers: shared hit=88367 read=6234277 dirtied=46
-> Parallel Hash (cost=3550.14..3550.14 rows=3423 width=4) (actual time=20.504..20.507 rows=2086 loops=5)
Buckets: 16384 Batches: 1 Memory Usage: 608kB
Buffers: shared hit=38315
-> Nested Loop (cost=82.91..3550.14 rows=3423 width=4) (actual time=0.348..19.665 rows=2086 loops=5)
Buffers: shared hit=38315
-> Parallel Bitmap Heap Scan on form f (cost=82.48..1104.44 rows=3255 width=4) (actual time=0.266..4.732 rows=1837 loops=5)
Recheck Cond: (template_id = 37201)
Heap Blocks: exact=309
Buffers: shared hit=1064
-> Bitmap Index Scan on form_id_templ_idx (cost=0.00..79.96 rows=10090 width=0) (actual time=0.955..0.955 rows=9187 loops=1)
Index Cond: (template_id = 37201)
Buffers: shared hit=29
-> Index Scan using form_version_form_id_idx on form_version fv (cost=0.43..0.73 rows=2 width=8) (actual time=0.007..0.008 rows=1 loops=9185)
Index Cond: (form_id = f.id)
Buffers: shared hit=37251
Planning Time: 5.398 ms
Execution Time: 253553.697 ms

Maxim Boguk, эти параметры не правили потому что в 9.5 этих параметров не было. Сейчас они дефолтные
#parallel_tuple_cost = 0.1 # same scale as above
#parallel_setup_cost = 1000.0 # same scale as above

Maxim Boguk,

повышение parallel_tuple_cost/parallel_setup_cost дало то, что
parallel seq_sсan заменился на seq_sсan, стало еще хуже


перебрал кучу разных комбинаций
seq_page_cost\random_page_cost

В районе этих значений
set seq_page_cost = 3.1;
set random_page_cost = 0.1;

запрос переходит на индекс
Nested Loop (cost=8473.39..30179095.28 rows=1516150 width=32) (actual time=64.501..964.653 rows=1438998 loops=1)
Buffers: shared hit=103340 read=41 dirtied=2
-> HashAggregate (cost=8472.81..8579.00 rows=10619 width=4) (actual time=64.433..68.463 rows=10438 loops=1)
Group Key: fv.id
Buffers: shared hit=38334
-> Nested Loop (cost=0.86..8446.27 rows=10619 width=4) (actual time=0.153..60.871 rows=10438 loops=1)
Buffers: shared hit=38334
-> Index Scan using form_id_templ_idx on form f (cost=0.43..1106.60 rows=10090 width=4) (actual time=0.085..11.469 rows=9189 loops=1)
Index Cond: (template_id = 37201)
Buffers: shared hit=1067
-> Index Scan using form_version_form_id_idx on form_version fv (cost=0.43..0.71 rows=2 width=8) (actual time=0.004..0.005 rows=1 loops=9189)
Index Cond: (form_id = f.id)
Buffers: shared hit=37267
-> Index Scan using cell_data_form_version_from_idx on cell_data cd (cost=0.57..2535.17 rows=30601 width=32) (actual time=0.044..0.069 rows=138 loops=10438)
Index Cond: (form_version_from = fv.id)
Buffers: shared hit=65006 read=41 dirtied=2
Planning Time: 3.999 ms
Execution Time: 1017.383 ms

Maxim Boguk,

если вернуть дефолтные настройки параллельности, то на индекс запрос переходит только при
set seq_page_cost = 5.1;
set random_page_cost = 0.1;