Re: Strange runtime partition pruning behaviour with 11.4

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