query.sql
application/sql
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)
*/