Re: [PATCH] Consider window functions when reordering GROUP BY items

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

In response to

Browse pgsql-hackers by date

  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