From: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Reordering DISTINCT keys to match input path's pathkeys
Date: Tue, 26 Nov 2024 00:28:52 +0000
Message-ID: <E1tFjRk-003XIl-L7@gemulon.postgresql.org> (raw)
Reordering DISTINCT keys to match input path's pathkeys
The ordering of DISTINCT items is semantically insignificant, so we
can reorder them as needed. In fact, in the parser, we absorb the
sorting semantics of the sortClause as much as possible into the
distinctClause, ensuring that one clause is a prefix of the other.
This can help avoid a possible need to re-sort.
In this commit, we attempt to adjust the DISTINCT keys to match the
input path's pathkeys. This can likewise help avoid re-sorting, or
allow us to use incremental-sort to save efforts.
For DISTINCT ON expressions, the parser already ensures that they
match the initial ORDER BY expressions. When reordering the DISTINCT
keys, we must ensure that the resulting pathkey list matches the
initial distinctClause pathkeys.
This introduces a new GUC, enable_distinct_reordering, which allows
the optimization to be disabled if needed.
Author: Richard Guo
Reviewed-by: Andrei Lepikhov
Discussion: https://postgr.es/m/CAMbWs48dR26cCcX0f=8bja2JKQPcU64136kHk=xekHT9xschiQ@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/a8ccf4e93a7eeaae66007bbf78cf9183ceb1b371
Modified Files
--------------
doc/src/sgml/config.sgml | 14 +
src/backend/optimizer/path/pathkeys.c | 38 ++
src/backend/optimizer/plan/planner.c | 456 +++++++++++++----------
src/backend/utils/misc/guc_tables.c | 10 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/optimizer/optimizer.h | 1 +
src/test/regress/expected/select_distinct.out | 132 +++++++
src/test/regress/expected/select_distinct_on.out | 125 +++++++
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/select_distinct.sql | 53 +++
src/test/regress/sql/select_distinct_on.sql | 40 ++
11 files changed, 672 insertions(+), 201 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: rguo@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Reordering DISTINCT keys to match input path's pathkeys
In-Reply-To: <E1tFjRk-003XIl-L7@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox