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
