Horton

low_mem_groupby_reorder.sql

application/sql

Download
Filename: low_mem_groupby_reorder.sql
Type: application/sql
Part: 0
Message: Re: POC: GROUP BY optimization 
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