CREATE TABLE btg2 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 500 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg3 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 1000 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg4 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 10000 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg5 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 100000 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg6 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 400000 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg7 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 500000 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg8 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 600000 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg9 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 700000 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg10 AS SELECT i::numeric % 10 AS x, i % 10 AS y, 'abc' || i % 10 AS z, i % 800000 AS w FROM generate_series(1, 1e6) AS i; CREATE TABLE btg AS SELECT i % 100 AS x, i % 100 AS y,'abc' || i % 10 AS z, i AS w FROM generate_series(1, 1e6) AS i; CREATE INDEX btg_x_y ON btg(x,y); CREATE INDEX btg2_x_y_idx ON btg2(x, y); CREATE INDEX btg3_x_y_idx ON btg3(x, y); CREATE INDEX btg4_x_y_idx ON btg4(x, y); CREATE INDEX btg5_x_y_idx ON btg5(x, y); CREATE INDEX btg6_x_y_idx ON btg6(x, y); CREATE INDEX btg7_x_y_idx ON btg7(x, y); CREATE INDEX btg8_x_y_idx ON btg8(x, y); CREATE INDEX btg9_x_y_idx ON btg9(x, y); CREATE INDEX btg10_x_y_idx ON btg10(x, y); ANALYZE btg; ANALYZE btg2; ANALYZE btg3; ANALYZE btg4; ANALYZE btg5; ANALYZE btg6; ANALYZE btg; ANALYZE btg7; ANALYZE btg8; ANALYZE btg9; ANALYZE btg10; --same 1_000_000 rows grouping it to different groups. select count(*) from (SELECT count(*) FROM btg GROUP BY w, x, y, z ORDER BY y, x); -- 1000000 select count(*) from (SELECT count(*) FROM btg2 GROUP BY w, x, y, z ORDER BY y, x); --500 select count(*) from (SELECT count(*) FROM btg3 GROUP BY w, x, y, z ORDER BY y, x); --1000 select count(*) from (SELECT count(*) FROM btg4 GROUP BY w, x, y, z ORDER BY y, x); -- 10000 select count(*) from (SELECT count(*) FROM btg5 GROUP BY w, x, y, z ORDER BY y, x); -- 100000 select count(*) from (SELECT count(*) FROM btg6 GROUP BY w, x, y, z ORDER BY y, x); -- 400000 select count(*) from (SELECT count(*) FROM btg7 GROUP BY w, x, y, z ORDER BY y, x); -- 500000 select count(*) from (SELECT count(*) FROM btg8 GROUP BY w, x, y, z ORDER BY y, x); -- 600000 select count(*) from (SELECT count(*) FROM btg9 GROUP BY w, x, y, z ORDER BY y, x); -- 700000 select count(*) from (SELECT count(*) FROM btg10 GROUP BY w, x, y, z ORDER BY y, x); -- 800000 select count(*) from (SELECT count(*) FROM btg GROUP BY w, x, y, z ORDER BY y, x); --------------------------------------------------------------- SET enable_hashagg = on; SET enable_seqscan = on; /* set work_mem to '64kB' is important for this test case!!! */ set work_mem to '64kB'; explain(analyze) SELECT count(*) FROM btg GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 --in pg17 --low_mem: 1255.791 ms --in pg16 --low_mem: 1584.873 ms explain(analyze) SELECT count(*) FROM btg GROUP BY z, y, w, x \watch i=0.1 c=10 --in pg17 --low_mem: 597.606 ms --in pg16 --low_mem: 1550.657 ms explain(analyze) SELECT count(*) FROM btg GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 --in pg17 --low_mem: 1521.120 ms --in pg16 --low_mem: 1971.047 ms explain(analyze) SELECT count(*) FROM btg2 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 --in pg17 --low_mem 764.965 ms --in pg16 --low_mem 1797.660 ms explain(analyze) SELECT count(*) FROM btg2 GROUP BY z, y, w, x \watch i=0.1 c=10 --in pg17 --low_mem 864.752 ms --in pg16 --low_mem 1814.652 ms explain(analyze) SELECT count(*) FROM btg2 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 --in pg17 --low_mem 760.244 ms --in pg16 --low_mem 2054.994 ms explain(analyze) SELECT count(*) FROM btg3 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 --in pg17 --low_mem 793.500 ms --in pg16 --low_mem 1873.979 ms explain(analyze) SELECT count(*) FROM btg3 GROUP BY z, y, w, x \watch i=0.1 c=10 --in pg17 --low_mem 915.215 ms --in pg16 --low_mem 1881.306 ms explain(analyze) SELECT count(*) FROM btg3 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 --in pg17 --low_mem 772.029 ms --in pg16 --low_mem 2189.227 ms explain(analyze) SELECT count(*) FROM btg4 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 --in pg17 --low_mem 843.426 ms --in pg16 --low_mem 1782.267 ms explain(analyze) SELECT count(*) FROM btg4 GROUP BY z, y, w, x \watch i=0.1 c=10 --in pg17 --low_mem 1175.817 ms --in pg16 --low_mem 1773.939 ms explain(analyze) SELECT count(*) FROM btg4 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 --in pg17 --low_mem 846.560 ms --in pg16 --low_mem 2137.784 ms explain(analyze) SELECT count(*) FROM btg5 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 --in pg17 --low_mem 675.586 ms --in pg16 --low_mem 1726.367 ms explain(analyze) SELECT count(*) FROM btg5 GROUP BY z, y, w, x \watch i=0.1 c=10 --in pg17 --low_mem 746.574 ms --in pg16 --low_mem 1693.483 ms explain(analyze) SELECT count(*) FROM btg5 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 --in pg17 --low_mem 702.361 ms --in pg16 --low_mem 2116.877 ms explain(analyze) SELECT count(*) FROM btg6 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 -- in pg17 --low_mem 1632.133 ms -- in pg16 --low_mem 1664.846 ms explain(analyze) SELECT count(*) FROM btg6 GROUP BY z, y, w, x \watch i=0.1 c=10 -- in pg17 --low_mem 671.089 ms -- in pg16 --low_mem 1626.804 ms explain(analyze) SELECT count(*) FROM btg6 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 --in pg17 --low_mem 2035.047 ms --in pg16 --low_mem 2078.280 ms explain(analyze) SELECT count(*) FROM btg7 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 -- in pg17 --low_mem 1620.115 ms -- in pg16 --low_mem: 1624.679 ms explain(analyze) SELECT count(*) FROM btg7 GROUP BY z, y, w, x \watch i=0.1 c=10 -- in pg17 --low_mem: 661.703 ms -- in pg16 --low_mem: 1600.430 ms explain(analyze) SELECT count(*) FROM btg7 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 -- in pg17 --low_mem: 2023.788 ms -- in pg16 --low_mem: 2024.906 ms explain(analyze) SELECT count(*) FROM btg8 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 -- in pg17 --low_mem: 1612.486 ms -- in pg16 --low_mem: 1612.022 ms explain(analyze) SELECT count(*) FROM btg8 GROUP BY z, y, w, x \watch i=0.1 c=10 -- in pg17 --low_mem: 645.047 ms -- in pg16 --low_mem: 1584.295 ms explain(analyze) SELECT count(*) FROM btg8 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 -- in pg17 --low_mem: 2018.166 ms -- in pg16 --low_mem: 2020.535 ms explain(analyze) SELECT count(*) FROM btg9 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 -- 501.217 ms --low_mem: 1601.448 ms -- in pg16 --low_mem: 1601.480 ms explain(analyze) SELECT count(*) FROM btg9 GROUP BY z, y, w, x \watch i=0.1 c=10 -- 331.132 ms --low_mem: 651.616 ms -- in pg16 --low_mem: 1566.856 ms explain(analyze) SELECT count(*) FROM btg9 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 -- 1981.715 ms --low_mem: 1994.944 ms -- in pg16 --low_mem: 1984.138 ms explain(analyze) SELECT count(*) FROM btg10 GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 -- in pg17 --low_mem: 1565.650 ms -- in pg16 --low_mem: 1593.570 ms explain(analyze) SELECT count(*) FROM btg10 GROUP BY z, y, w, x \watch i=0.1 c=10 -- in pg17 --low_mem: 650.556 ms -- in pg16 --low_mem: 1566.801 ms explain(analyze) SELECT count(*) FROM btg10 GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 -- in pg17 --low_mem: 1962.906 ms -- in pg16 --low_mem: 1995.242 ms