| 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-21 14:44:59 |
| Message-ID: | CAAAe_zDG4rV=O-qR_vd=GseSiBauotYFnhtsPn38v61EmWCJXg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi Zhenglong,
> 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.
Thanks for v6. I read it again in planning order: where a sorted
path is kept, how eager aggregation below the join uses it, which
GROUP BY orders become candidates, which orders the parallel paths
are sorted in, and what turning the GUC off does. Please correct
me if I got any of it wrong.
truncate_useless_pathkeys():
- A path whose leading keys are GROUP BY columns, in any order,
sorted in the other direction now counts as useful, so it is no
longer thrown away before grouping. This only decides whether
the path is kept; it does not change its order.
- It counts only if the operator family has the sort operator for
that direction, so both repro queries get master's plans and
results.
generate_grouped_paths(), for eager aggregation:
- No candidates are made here. For each input path, the leading
grouping keys that the input is sorted on, in either direction,
are changed to the input's direction; the key order is kept.
- This is fine, as the partial aggregation here compares the keys
only for equality, and the grouping step above the join still
provides the final GROUP BY and its order.
get_useful_group_keys_orderings():
- Existing candidates: the original order, which follows ORDER BY,
and the input path's order. The latter is now also made when
the direction differs: the GROUP BY keys are changed to the
input's direction, and it stops where they can't be sorted that
way.
- New candidate: the GROUP BY keys reordered to the first window's
PARTITION BY and ORDER BY, unless another candidate is already
the same. It is kept even when only some of the GROUP BY keys
match the window.
gather_grouping_paths():
- Existing order: the original order. Parallel paths, such as
partial aggregates, are sorted in it before Gather Merge.
- Added order: the same reordering to the first window, tried as
well as the original one. So the workers do the sort for the
window below Gather Merge, instead of the leader above it.
Turning enable_group_by_reordering off turns all of this off:
- truncate_useless_pathkeys(): a path sorted in the opposite
direction is not kept for GROUP BY.
- generate_grouped_paths(): it sorts only in the original direction.
- get_useful_group_keys_orderings(): only the original order is
offered.
- gather_grouping_paths(): no window order is added, so parallel
paths are sorted only in the original order.
So the plans are those of master with the GUC off, and planning is
no slower than on master, as the last column of your table shows.
With it on, as I said in my last mail, the extra planning time should
not matter for a table with that many partitions.
> 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.
I don't think they are dead code. I turned both into Asserts on a
cassert build, and each fails on one of the attached queries, while
v6 as posted runs them fine:
- group_keys_reorder_by_pathkeys(): a cross-type operator family
where the window's ORDER BY type has the reverse operator but
the GROUP BY type doesn't, through the window candidate.
- adjust_group_pathkeys_to_input(): eager aggregation with an
operator class that has only = and >, where a forward index
scan is kept as useful for merging.
So I'd keep them as runtime checks.
The code looks good to me. For the docs, the description of
enable_group_by_reordering in config.sgml still talks only about the
order of a child node's keys, though the GUC now also covers their
direction and the window's order. How about updating it along these
lines?
Controls if the query planner will produce a plan which will
provide GROUP BY keys sorted in the order of keys of a child
node of the plan, such as an index scan, in either sort
direction, or in the order required by window functions.
Thank you for the careful work on it.
Best regards,
Henson
| Attachment | Content-Type | Size |
|---|---|---|
| guards-repro.sql | application/octet-stream | 3.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andrey Borodin | 2026-11-21 15:26:08 | Re: Allow isolation test steps to be declared inline. |
| Previous Message | Fujii Masao | 2026-11-21 14:26:17 | Re: [PATCH] Fix two client-side NULL dereferences on malformed result sets (libpq, pg_receivewal) |