plans-14.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                                                     
-------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1869.39..2823.15 rows=8 width=52)
   Workers Planned: 2
   ->  Parallel Append  (cost=869.39..1822.35 rows=4 width=52)
         ->  GroupAggregate  (cost=998.04..1178.25 rows=4 width=52)
               Group Key: pagg_tab_ml.a
               Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
               ->  Sort  (cost=998.04..1028.04 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=869.39..1019.56 rows=3 width=52)
               Group Key: pagg_tab_ml_5.a
               Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
               ->  Sort  (cost=869.39..894.39 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=682.63..802.77 rows=3 width=52)
               Group Key: pagg_tab_ml_2.a
               Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
               ->  Sort  (cost=682.63..702.63 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)

SET max_parallel_workers_per_gather = 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                                                  
-------------------------------------------------------------------------------------------------------------
 Append  (cost=998.04..3000.64 rows=10 width=52)
   ->  GroupAggregate  (cost=998.04..1178.25 rows=4 width=52)
         Group Key: pagg_tab_ml.a
         Filter: (avg(pagg_tab_ml.b) < '3'::numeric)
         ->  Sort  (cost=998.04..1028.04 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=682.63..802.77 rows=3 width=52)
         Group Key: pagg_tab_ml_2.a
         Filter: (avg(pagg_tab_ml_2.b) < '3'::numeric)
         ->  Sort  (cost=682.63..702.63 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=869.39..1019.56 rows=3 width=52)
         Group Key: pagg_tab_ml_5.a
         Filter: (avg(pagg_tab_ml_5.b) < '3'::numeric)
         ->  Sort  (cost=869.39..894.39 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)

RESET max_parallel_workers_per_gather;