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

From: Keyerror Smart <smartkeyerror(at)gmail(dot)com>
To: assam258(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-20 02:14:13
Message-ID: CAD=-kXYNuEKPyQf3k5y_rEj3+Q-JDbr1=OL9DDkW8q8KnNq5JQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Henson,

> Master runs it fine, so it's the latter. With that check on v5, both
> queries above get master's plans and results.

Thanks for the repro. I could reproduce both queries failing on v5, and
also with only 0001 applied, while master returns 21 and 3 rows. v6 is
attached.

As you suggested, count_common_leading_group_pathkeys() now also requires
the sort operator, using pathkey_has_sort_operator(), for a pathkey that
matches a grouping key only when the direction is ignored. So the forward
index scan is no longer kept as useful for grouping, and both queries get
master's plans and results. Pathkeys that match a grouping key exactly
don't go through the check, so they cost nothing extra. I added your two
queries to the btg_color test in aggregates.sql; they fail without the
fix.

This also changes the plan of the eager aggregation test in
eager_aggregate.sql. It now scans the index backward with an incremental
sort, the same plan as on master, instead of scanning it forward and
doing a full sort.

About the other two guards, in group_keys_reorder_by_pathkeys() and
adjust_group_pathkeys_to_input(): I added a WARNING to both, and with
this fix make check reaches neither of them. I kept them for now, as I
can't be sure that no other path keeps such pathkeys, e.g. as useful for
merging. If you or a committer think they are dead code, I'm fine with
removing them.

> 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.

I measured it too, with release builds of master (f03a0f87963) and v6,
on a table hash-partitioned into 256 partitions with indexes on (a, b)
and (b, c), and enable_partitionwise_aggregate on. Median planning
times in ms over 90 runs:

master v6 v6, reordering
off
GROUP BY a, b (a is the partition key) 1.56 2.15 1.43
GROUP BY a, b ORDER BY a, b DESC 1.63 2.46 1.52
GROUP BY b, c (not partitionwise) 1.18 1.45 1.16
GROUP BY a, b, window on (b, a DESC) 1.58 2.86 1.46
GROUP BY b, c, window on (c, b DESC) 1.10 1.48 1.10

So it's 1.2x to 1.8x, or 0.3 to 1.3 ms in total, in line with what you
saw, and with enable_group_by_reordering off v6 plans as fast as master.
With only 2000 rows per partition these queries already take 20 to
70 ms to execute, so I agree it should not matter.

Regards,
Zhenglong Li

Attachment Content-Type Size
v6-0001-Ignore-sort-direction-when-matching-GROUP-BY-keys.patch application/octet-stream 48.8 KB
v6-0002-Consider-window-functions-when-reordering-GROUP-B.patch application/octet-stream 48.8 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message John Naylor 2026-11-20 02:24:33 Re: Keep TIDBitmap pagetable memory across bitmap scans
Previous Message Kyotaro Horiguchi 2026-11-20 02:12:11 Re: Injection point: fix race condition between attach and cleanup