Skip a redundant singleton GROUP BY node
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253848psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 19, 2026 at 06:16 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253848_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253848_1 && git checkout t253848_1Patchset v1 (message #1) is on t253848_1
Hi all,
This two-patch series avoids a plain GROUP BY step when the planner can
prove that every input row already forms its own group.
The motivation is not to encourage redundant SQL. ORMs, report builders,
and generated query templates often retain GROUP BY keys that are useful
to the query contract even when a primary key already makes each input row
a singleton. For such queries the grouping node only repeats a partitioning
that is already known to produce one row per group.
The proof is deliberately narrow. The input must be a single ordinary base
relation or partitioned table. For a partitioned table, the unique index
must include the partition key, so covering it in the grouping key proves
cross-partition uniqueness as well. The query must be a plain GROUP BY with
no aggregates, HAVING, window functions, set operations, DISTINCT, SRFs, or
row locking. There must be an immediate, non-partial, non-expression unique
index whose key columns are covered by simple grouping Vars. A NULLS
DISTINCT unique index also requires each key column to be NOT NULL; NULLS
NOT DISTINCT removes that requirement. The index and grouping key must
agree on equality semantics, including opfamily and collation. Additional
grouping expressions are safe because they can only subdivide the singleton
groups.
When the proof holds, the planner adds projection paths to the grouping
upper relation instead of building ordinary aggregate paths. It does not
rewrite the parse tree. GetForeignUpperPaths and create_upper_paths_hook
are still called, and set_cheapest is performed after those hooks, so
extensions retain the same upper-relation entry point.
Patch 1 extracts the existing unique-index/GROUP BY matching logic in
remove_useless_groupby_columns() into a helper. It is behavior-preserving
and lets the new proof share the NOT NULL, NULLS NOT DISTINCT, opfamily,
and collation checks rather than maintaining a second implementation.
Patch 2 adds the planner optimization, documentation, and regression
coverage. New tests cover opfamily and collation mismatches, prepared-plan
invalidation after DROP INDEX and DROP NOT NULL, and implicit and degenerate
grouping cases. make check passes all core regression tests.
Benchmarks use paired release builds (--disable-debug --disable-cassert,
-O2), each on its own disposable cluster. On synthetic unlogged workloads,
GROUP BY on a 300k-row primary-key table drops from 83.0 ms to 14.1 ms
median. Unique-key plus expression cases improve 2.5x-3.5x, and a forced
hash-aggregate spill case improves from 272.2 ms to 40.5 ms. Non-target
aggregate/join cases retain identical plan shapes; sampled execution medians
change by +0.2%-+3.0%, and 51-run planning medians are not higher than
baseline in this run. I am not claiming zero runtime change for non-target
workloads.
Open questions I would especially appreciate review on:
* Is the zero-method GroupPathExtraData contract (i.e. no aggregate
methods present) sufficient for FDWs and upper-path hooks that see this
optimized grouped relation?
* Should the helper live in indxpath.c, or is there a better home now
that both initsplan.c and the path-level proof use it?
Expression indexes, partial unique predicates, join-output uniqueness, and
LIMIT-driven opportunities are intentionally outside this series.
Thanks,
Xiangxin Zeng