On Thu, Apr 18, 2024 at 6:58 PM Alexander Korotkov <aekorot...@gmail.com> wrote: > > Thank you for the fixes you've proposed. I didn't look much into > details yet, but I think the main concern Tom expressed in [1] is > whether the feature is reasonable at all. I think at this stage the > most important thing is to come up with convincing examples showing > how huge performance benefits it could cause. I will return to this > later today and will try to provide some convincing examples. >
hi. I found a case where it improved performance. +-- GROUP BY optimization by reorder columns +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,10000) AS i; +CREATE INDEX abc ON btg(x,y); +ANALYZE btg; + I change +FROM generate_series(1,10000) AS i; to + FROM generate_series(1, 1e6) AS i; Then I found out about these 2 queries performance improved a lot. A: explain(analyze) SELECT count(*) FROM btg GROUP BY w, x, y, z ORDER BY y, x \watch i=0.1 c=10 B: explain(analyze) SELECT count(*) FROM btg GROUP BY w, x, z, y ORDER BY y, x, z, w \watch i=0.1 c=10 set (enable_seqscan,enable_hashagg) from on to off: queryA execution time from 1533.013 ms to 533.430 ms queryB execution time from 1996.817 ms to 497.020 ms