Re: Strange runtime partition pruning behaviour with 11.4 - Mailing list pgsql-performance

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