Hi,
Am 03.08.19 um 15:16 schrieb MichaelDBA:
I too am a bit perplexed by why runtime partition pruning does not
seem to work with this example. Anybody got any ideas of this?
please don't top-posting.
it's posible to rewrite the query to:
test=# explain analyse select count(*) from foo_bar_baz as fbb where
foo_id = (select foo_id from foo where foo_name = 'eeny');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=15880.63..15880.64 rows=1 width=8) (actual
time=48.447..48.448 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Seq Scan on foo (cost=0.00..24.50 rows=6 width=4) (actual
time=0.243..0.246 rows=1 loops=1)
Filter: ((foo_name)::text = 'eeny'::text)
Rows Removed by Filter: 5
-> Gather (cost=15855.92..15856.13 rows=2 width=8) (actual
time=48.376..51.468 rows=3 loops=1)
Workers Planned: 2
Params Evaluated: $0
Workers Launched: 2
-> Partial Aggregate (cost=14855.92..14855.93 rows=1
width=8) (actual time=42.600..42.600 rows=1 loops=3)
-> Parallel Append (cost=0.00..13883.01 rows=389162
width=0) (actual time=0.139..34.914 rows=83500 loops=3)
-> Parallel Bitmap Heap Scan on foo_bar_baz_0
fbb (cost=4.23..14.73 rows=6 width=0) (never executed)
Recheck Cond: (foo_id = $0)
-> Bitmap Index Scan on foo_bar_baz_0_pkey
(cost=0.00..4.23 rows=10 width=0) (never executed)
Index Cond: (foo_id = $0)
-> Parallel Seq Scan on foo_bar_baz_2 fbb_2
(cost=0.00..3865.72 rows=178218 width=0) (never executed)
Filter: (foo_id = $0)
-> Parallel Seq Scan on foo_bar_baz_1 fbb_1
(cost=0.00..3195.62 rows=147250 width=0) (actual time=0.129..24.735
rows=83500 loops=3)
Filter: (foo_id = $0)
-> Parallel Seq Scan on foo_bar_baz_3 fbb_3
(cost=0.00..2334.49 rows=107559 width=0) (never executed)
Filter: (foo_id = $0)
-> Parallel Seq Scan on foo_bar_baz_4 fbb_4
(cost=0.00..1860.95 rows=85756 width=0) (never executed)
Filter: (foo_id = $0)
-> Parallel Seq Scan on foo_bar_baz_5 fbb_5
(cost=0.00..665.69 rows=30615 width=0) (never executed)
Filter: (foo_id = $0)
Planning Time: 12.648 ms
Execution Time: 52.621 ms
(27 rows)
test=*#
I know, that's not a solution, but a workaround. :-(
(pg 12beta2 and also with PostgreSQL 11.4 (2ndQPG 11.4r1.6.7))
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
