Re: Strange runtime partition pruning behaviour with 11.4

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux