plans-15.log

text/x-log

Download

-- Without ORDER BY clause, to test Gather at top-most path
EXPLAIN (COSTS ON)
SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Append  (cost=779.41..2490.61 rows=10 width=52)
   ->  GroupAggregate  (cost=779.41..959.62 rows=4 width=52)
         Group Key: pagg_tab_ml.a
         Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
         ->  Sort  (cost=779.41..809.41 rows=12000 width=13)
               Sort Key: pagg_tab_ml.a
               ->  Seq Scan on pagg_tab_ml_p1 pagg_tab_ml  (cost=0.00..185.00 rows=12000 width=13)
   ->  GroupAggregate  (cost=560.28..680.42 rows=3 width=52)
         Group Key: pagg_tab_ml_2.a
         Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
         ->  Sort  (cost=560.28..580.28 rows=8000 width=13)
               Sort Key: pagg_tab_ml_2.a
               ->  Append  (cost=0.00..164.00 rows=8000 width=13)
                     ->  Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2  (cost=0.00..93.00 rows=6000 width=13)
                     ->  Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3  (cost=0.00..31.00 rows=2000 width=13)
   ->  GroupAggregate  (cost=700.34..850.52 rows=3 width=52)
         Group Key: pagg_tab_ml_5.a
         Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
         ->  Sort  (cost=700.34..725.34 rows=10000 width=13)
               Sort Key: pagg_tab_ml_5.a
               ->  Append  (cost=0.00..205.00 rows=10000 width=13)
                     ->  Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5  (cost=0.00..108.00 rows=7000 width=13)
                     ->  Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6  (cost=0.00..47.00 rows=3000 width=13)
(23 rows)

SET parallel_setup_cost = 0;
EXPLAIN (COSTS ON)
SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Gather  (cost=700.34..1531.76 rows=8 width=52)
   Workers Planned: 2
   ->  Parallel Append  (cost=700.34..1530.96 rows=4 width=52)
         ->  GroupAggregate  (cost=779.41..959.62 rows=4 width=52)
               Group Key: pagg_tab_ml.a
               Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
               ->  Sort  (cost=779.41..809.41 rows=12000 width=13)
                     Sort Key: pagg_tab_ml.a
                     ->  Seq Scan on pagg_tab_ml_p1 pagg_tab_ml  (cost=0.00..185.00 rows=12000 width=13)
         ->  GroupAggregate  (cost=700.34..850.52 rows=3 width=52)
               Group Key: pagg_tab_ml_5.a
               Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
               ->  Sort  (cost=700.34..725.34 rows=10000 width=13)
                     Sort Key: pagg_tab_ml_5.a
                     ->  Append  (cost=0.00..205.00 rows=10000 width=13)
                           ->  Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5  (cost=0.00..108.00 rows=7000 width=13)
                           ->  Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6  (cost=0.00..47.00 rows=3000 width=13)
         ->  GroupAggregate  (cost=560.28..680.42 rows=3 width=52)
               Group Key: pagg_tab_ml_2.a
               Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
               ->  Sort  (cost=560.28..580.28 rows=8000 width=13)
                     Sort Key: pagg_tab_ml_2.a
                     ->  Append  (cost=0.00..164.00 rows=8000 width=13)
                           ->  Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2  (cost=0.00..93.00 rows=6000 width=13)
                           ->  Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3  (cost=0.00..31.00 rows=2000 width=13)
(25 rows)