Avoid unnecessary processing of DISTINCT clause (Was: Unique Keys)
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:t52704psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 06:19 AM.
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 t52704_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 t52704_1 && git checkout t52704_1Patchset v1 (message #1) is on t52704_1
[1]: /messages/by-id/7mlamswjp81p.fsf@e18c07352.et15sqa
produce distinct rows, in order to avoid the DISTINCT step of planning. It
does so by conveying the uniqueness information ("unique keys") from
individual table/index scans to the upper planner. The concept is similar to
"path keys", which describe ordering of the path output. However, unlike "path
keys", the "unique keys" do not participate (AFAICS) in planning decisions in
the scan/join planning.
Here I try to introduce a top-down approach and deduce the path distinctness
from the existing information in the path tree. As the patch does not add any
information that the planner would have to propagate from lower to upper
nodes, it's less invasive than [1]/messages/by-id/7mlamswjp81p.fsf@e18c07352.et15sqa. (Unlike [1]/messages/by-id/7mlamswjp81p.fsf@e18c07352.et15sqa, I haven't implemented the
"single row optimization" yet, but I see no reason why it shouldn't be
possible.)
For index / scan path, the patch obviously uses unique indexes as the "source
of uniqueness". For joins, it relies on the JoinPath.inner_unique field. The
theory is that a join produces unique rows if the 1) outer path does and 2) no
more than one inner row matches each outer row (i.e. the inner path does not
"duplicate" the outer rows). For more details, please see the commit message
and the patch itself.
Finally it's checked whether the final scan/join output would be unique even
if it contained only the DISTINCT expressions. If it does, no additional
processing (such as UniquePath or AggPath) is needed.
Is anything wrong about this approach, whether conceptually or in details?
[1]: /messages/by-id/7mlamswjp81p.fsf@e18c07352.et15sqa
--
Antonin Houska
Web: https://www.cybertec-postgresql.com