DROP TABLE IF EXISTS test CASCADE;
CREATE TABLE test(firstname text, lastname text, address text, age integer);
INSERT INTO test (SELECT (random()*100 || '_fname')::text,
  (random()*1000 || '_lname')::text,
  (random()*100 || '_zip' || random()*1000 || 'street')::text,
  random()*80::integer
FROM generate_series(1,1E6) AS g);
CREATE INDEX test_idx ON test (address,firstname,lastname);
VACUUM ANALYZE;

SET max_parallel_workers_per_gather = 0;

-- Case 1: not-optimised query
SET enable_group_by_reordering = f;
EXPLAIN (COSTS ON, ANALYZE)
SELECT firstname,lastname,address, count(address) FROM test
WHERE address < '5'
GROUP BY firstname,lastname,address;
SET enable_group_by_reordering = t;
EXPLAIN (COSTS ON, ANALYZE)
SELECT firstname,lastname,address, count(address) FROM test
WHERE address < '5'
GROUP BY firstname,lastname,address;

-- Case 2: Accidental pathkeys with MergeJoin allow to avoid sorting
SET enable_hashjoin = f;
SET enable_group_by_reordering = f;
EXPLAIN (COSTS ON, ANALYZE)
SELECT t1.lastname, avg(t1.age) FROM test t1, test t2
WHERE t1.age=t2.age AND t1.lastname = t2.lastname
GROUP BY t1.firstname,t1.age,t1.lastname;

SET enable_group_by_reordering = t;
EXPLAIN (COSTS ON, ANALYZE)
SELECT t1.lastname, avg(t1.age) FROM test t1, test t2
WHERE t1.age=t2.age AND t1.lastname = t2.lastname
GROUP BY t1.firstname,t1.age,t1.lastname;
RESET enable_hashjoin;


CREATE INDEX test_idx2 ON test (age,address);
CREATE INDEX test_idx3 ON test (address,age);
VACUUM ANALYZE;

-- Case 3: We can't find optimal group-by order because of different scan paths (or we can't use more optimal index)
SET enable_group_by_reordering = f;
EXPLAIN (COSTS ON, ANALYZE)
SELECT age,address, count(address) FROM test
WHERE address < '4' AND age > 10
GROUP BY age,address;

SET enable_group_by_reordering = t;
EXPLAIN (COSTS ON, ANALYZE)
SELECT age,address, count(address) FROM test
WHERE address < '4' AND age > 10
GROUP BY age,address;

-- Case 4: GroupAgg sometimes better than HashAgg
SET enable_group_by_reordering = f;
EXPLAIN (COSTS ON, ANALYZE)
SELECT age,address, count(address) FROM test
WHERE address < '3' AND age > 10
GROUP BY age,address;

SET enable_group_by_reordering = t;
EXPLAIN (COSTS ON, ANALYZE)
SELECT age,address, count(address) FROM test
WHERE address < '3' AND age > 10
GROUP BY age,address;

/*
---------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=94986.20..105263.25 rows=456758 width=101) (actual time=1231.681..1627.708 rows=450582 loops=1)
   Group Key: firstname, lastname, address
   ->  Sort  (cost=94986.20..96128.09 rows=456758 width=93) (actual time=1231.666..1345.434 rows=450582 loops=1)
         Sort Key: firstname, lastname, address
         Sort Method: external merge  Disk: 45960kB
         ->  Seq Scan on test  (cost=0.00..28630.00 rows=456758 width=93) (actual time=0.011..231.253 rows=450582 loops=1)
               Filter: (address < '5'::text)
               Rows Removed by Filter: 549418
 Planning Time: 0.244 ms
 Execution Time: 1640.978 ms
(10 rows)

SET
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.55..44696.97 rows=456758 width=101) (actual time=0.061..458.483 rows=450582 loops=1)
   Group Key: address, firstname, lastname
   ->  Index Only Scan using test_idx on test  (cost=0.55..35561.82 rows=456758 width=93) (actual time=0.052..173.004 rows=450582 loops=1)
         Index Cond: (address < '5'::text)
         Heap Fetches: 0
 Planning Time: 0.161 ms
 Execution Time: 468.441 ms
(7 rows)

SET
SET
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=362334.94..362646.17 rows=12449 width=84) (actual time=8205.668..9856.220 rows=1000000 loops=1)
   Group Key: t1.firstname, t1.age, t1.lastname
   ->  Sort  (cost=362334.94..362366.06 rows=12449 width=52) (actual time=8205.648..8542.865 rows=1000000 loops=1)
         Sort Key: t1.firstname, t1.age, t1.lastname
         Sort Method: external merge  Disk: 62352kB
         ->  Merge Join  (cost=343863.69..361488.18 rows=12449 width=52) (actual time=4335.541..5913.685 rows=1000000 loops=1)
               Merge Cond: ((t1.age = t2.age) AND (t1.lastname = t2.lastname))
               ->  Sort  (cost=194149.84..196649.84 rows=1000000 width=52) (actual time=2186.263..2469.783 rows=1000000 loops=1)
                     Sort Key: t1.age, t1.lastname
                     Sort Method: external merge  Disk: 61904kB
                     ->  Seq Scan on test t1  (cost=0.00..26130.00 rows=1000000 width=52) (actual time=0.023..191.788 rows=1000000 loops=1)
               ->  Materialize  (cost=149713.84..154713.84 rows=1000000 width=28) (actual time=2149.266..2612.161 rows=1000000 loops=1)
                     ->  Sort  (cost=149713.84..152213.84 rows=1000000 width=28) (actual time=2149.260..2408.290 rows=1000000 loops=1)
                           Sort Key: t2.age, t2.lastname
                           Sort Method: external merge  Disk: 37352kB
                           ->  Seq Scan on test t2  (cost=0.00..26130.00 rows=1000000 width=28) (actual time=0.124..194.054 rows=1000000 loops=1)
 Planning Time: 0.316 ms
 Execution Time: 9919.260 ms
(18 rows)

SET
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=343865.11..362328.48 rows=12449 width=84) (actual time=4777.455..8003.005 rows=1000000 loops=1)
   Group Key: t1.age, t1.lastname, t1.firstname
   ->  Incremental Sort  (cost=343865.11..362048.38 rows=12449 width=52) (actual time=4777.442..6787.559 rows=1000000 loops=1)
         Sort Key: t1.age, t1.lastname, t1.firstname
         Presorted Key: t1.age, t1.lastname
         Full-sort Groups: 31250  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
         ->  Merge Join  (cost=343863.69..361488.18 rows=12449 width=52) (actual time=4777.355..6365.671 rows=1000000 loops=1)
               Merge Cond: ((t1.age = t2.age) AND (t1.lastname = t2.lastname))
               ->  Sort  (cost=194149.84..196649.84 rows=1000000 width=52) (actual time=2581.845..2866.407 rows=1000000 loops=1)
                     Sort Key: t1.age, t1.lastname
                     Sort Method: external merge  Disk: 61904kB
                     ->  Seq Scan on test t1  (cost=0.00..26130.00 rows=1000000 width=52) (actual time=0.035..227.627 rows=1000000 loops=1)
               ->  Materialize  (cost=149713.84..154713.84 rows=1000000 width=28) (actual time=2195.497..2660.839 rows=1000000 loops=1)
                     ->  Sort  (cost=149713.84..152213.84 rows=1000000 width=28) (actual time=2195.492..2458.836 rows=1000000 loops=1)
                           Sort Key: t2.age, t2.lastname
                           Sort Method: external merge  Disk: 37352kB
                           ->  Seq Scan on test t2  (cost=0.00..26130.00 rows=1000000 width=28) (actual time=0.049..193.362 rows=1000000 loops=1)
 Planning Time: 0.321 ms
 Execution Time: 8055.128 ms
(19 rows)

RESET
CREATE INDEX
CREATE INDEX
VACUUM
SET
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.55..49035.31 rows=292452 width=57) (actual time=0.040..395.848 rows=295911 loops=1)
   Group Key: age, address
   ->  Index Only Scan using test_idx2 on test  (cost=0.55..43917.40 rows=292452 width=49) (actual time=0.031..249.137 rows=295911 loops=1)
         Index Cond: ((age > 10) AND (address < '4'::text))
         Heap Fetches: 0
 Planning Time: 0.215 ms
 Execution Time: 401.967 ms
(7 rows)

SET
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.55..23926.56 rows=292452 width=57) (actual time=0.076..275.953 rows=295911 loops=1)
   Group Key: address, age
   ->  Index Only Scan using test_idx3 on test  (cost=0.55..18808.65 rows=292452 width=49) (actual time=0.069..132.790 rows=295911 loops=1)
         Index Cond: ((address < '4'::text) AND (age > 10))
         Heap Fetches: 0
 Planning Time: 0.170 ms
 Execution Time: 282.227 ms
(7 rows)

SET
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=33465.59..39281.03 rows=196925 width=57) (actual time=188.885..313.148 rows=200578 loops=1)
   Group Key: age, address
   Planned Partitions: 4  Batches: 21  Memory Usage: 8249kB  Disk Usage: 11880kB
   ->  Index Only Scan using test_idx3 on test  (cost=0.55..12665.39 rows=196925 width=49) (actual time=0.044..84.982 rows=200578 loops=1)
         Index Cond: ((address < '3'::text) AND (age > 10))
         Heap Fetches: 0
 Planning Time: 0.182 ms
 Execution Time: 318.455 ms
(8 rows)

SET
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.55..16111.58 rows=196925 width=57) (actual time=0.050..177.452 rows=200578 loops=1)
   Group Key: address, age
   ->  Index Only Scan using test_idx3 on test  (cost=0.55..12665.39 rows=196925 width=49) (actual time=0.042..80.740 rows=200578 loops=1)
         Index Cond: ((address < '3'::text) AND (age > 10))
         Heap Fetches: 0
 Planning Time: 0.181 ms
 Execution Time: 181.673 ms
(7 rows)

 */
