| From: | Henson Choi <assam258(at)gmail(dot)com> |
|---|---|
| To: | Keyerror Smart <smartkeyerror(at)gmail(dot)com> |
| Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: [PATCH] Consider window functions when reordering GROUP BY items |
| Date: | 2026-11-16 04:52:52 |
| Message-ID: | CAAAe_zAttEkQb0kbbC08z_BoQRL3O32WeQsKpj6+OwuzrzYgJQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi Zhenglong,
> Thanks for the thorough review and the +1. v5 is attached.
Thanks for v5. With both patches applied on master, make check
passes.
> Could you share the merge join query that still fails on v4? I tried a
> merge join over a backward index scan with the same operator class, and it
> worked both on master and with v4-0003.
Here it is. Both queries fail on v5 too with default settings, while
master returns 21 and 3 rows.
CREATE TYPE nh AS ENUM ('r', 'g', 'b');
CREATE FUNCTION nh_lt(nh, nh) RETURNS bool
LANGUAGE internal IMMUTABLE STRICT AS 'enum_lt';
CREATE FUNCTION nh_eq(nh, nh) RETURNS bool
LANGUAGE internal IMMUTABLE STRICT AS 'enum_eq';
CREATE FUNCTION nh_cmp(nh, nh) RETURNS int
LANGUAGE internal IMMUTABLE STRICT AS 'enum_cmp';
CREATE OPERATOR <<< (leftarg = nh, rightarg = nh, function = nh_lt);
CREATE OPERATOR === (leftarg = nh, rightarg = nh, function = nh_eq,
merges);
CREATE OPERATOR CLASS nh_ops DEFAULT FOR TYPE nh USING btree AS
OPERATOR 1 <<<, OPERATOR 3 ===, FUNCTION 1 nh_cmp(nh, nh);
CREATE TABLE tn (c nh, e int);
INSERT INTO tn SELECT (ARRAY['r', 'g', 'b'])[1 + i % 3]::nh, i % 7
FROM generate_series(1, 3000) i;
CREATE INDEX ON tn (c DESC) INCLUDE (e);
CREATE TABLE un (c nh);
INSERT INTO un SELECT (ARRAY['r', 'g', 'b'])[1 + i % 3]::nh
FROM generate_series(1, 30) i;
VACUUM ANALYZE tn, un;
SELECT t.c, t.e, count(*) FROM tn t JOIN un u ON t.c === u.c
GROUP BY t.c, t.e;
SELECT t.c, count(DISTINCT t.e) FROM tn t JOIN un u ON t.c === u.c
GROUP BY t.c;
It needs one more sort key after the index column, from GROUP BY or a
DISTINCT aggregate. The forward index scan is then kept as useful for
grouping, and the merge join fails sorting the other input by c DESC.
> If your query also fails on master,
> the guard can't make things consistent and I'm fine dropping it. If not,
> maybe it is better to check the operator once in
> count_common_leading_group_pathkeys(), so that such pathkeys are not kept.
Master runs it fine, so it's the latter. With that check on v5,
both queries above get master's plans and results. I have gone
through the overall flow of the code, and in broad terms it looks
right to me. I will take more time on the details, and keep looking
for regressions when the revised patch comes.
One thing so far: with partitionwise aggregation over 256 partitions
with indexes, planning time on v5 grows up to 2x over master, as the
orderings are considered for each partition. A table with 256
partitions holds a lot of data, though, so a few ms of planning should
not matter there.
> Either way, I agree it is the committers' decision.
Agreed.
Best regards,
Henson
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Henson Choi | 2026-11-16 05:00:53 | [DEPARSE] Columns added to a function's result type shift its column aliases |
| Previous Message | Amit Kapila | 2026-11-16 04:34:52 | Re: Assertion failure in update_local_synced_slot(), slotsync.c:364 |