Re: POC: GROUP BY optimization

On 24.04.2024 13:25, jian he wrote:
> hi.
> I found an interesting case.
>
> CREATE TABLE t1 AS
> SELECT (i % 10)::numeric AS x,(i % 10)::int8 AS y,'abc' || i % 10 AS
> z, i::int4 AS w
> FROM generate_series(1, 100) AS i;
> CREATE INDEX t1_x_y_idx ON t1 (x, y);
> ANALYZE t1;
> SET enable_hashagg = off;
> SET enable_seqscan = off;
>
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY x,z,y,w;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY x,w,y,z;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY x,z,w,y;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY x,w,z,y;
> the above part will use:
> -> Incremental Sort
> Sort Key: x, $, $, $
> Presorted Key: x
> -> Index Scan using t1_x_y_idx on t1
>
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY z,y,w,x;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY w,y,z,x;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY y,z,x,w;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY y,w,x,z;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY y,x,z,w;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY y,x,w,z;
>
> these will use:
> -> Incremental Sort
> Sort Key: x, y, $, $
> Presorted Key: x, y
> -> Index Scan using t1_x_y_idx on t1
>
> I guess this is fine, but not optimal?
It looks like a bug right now - in current implementation we don't
differentiate different orders. So:
1. Applying all the patches from the thread which I proposed as an
answer to T.Lane last rebuke - does behavior still the same?.
2. Could you try to find the reason?

--
regards,
Andrei Lepikhov
Postgres Professional