From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Use Limit instead of Unique to implement DISTINCT, when possible
Date: Fri, 28 Oct 2022 10:08:16 +0000
Message-ID: <E1ooMHf-0019kV-QX@gemulon.postgresql.org> (raw)
Use Limit instead of Unique to implement DISTINCT, when possible
When all of the query's DISTINCT pathkeys have been marked as redundant
due to EquivalenceClasses existing which contain constants, we can just
implement the DISTINCT operation on a query by just limiting the number of
returned rows to 1 instead of performing a Unique on all of the matching
(duplicate) rows.
This applies in cases such as:
SELECT DISTINCT col,col2 FROM tab WHERE col = 1 AND col2 = 10;
If there are any matching rows, then they must all be {1,10}. There's no
point in fetching all of those and running a Unique operator on them to
leave only a single row. Here we effectively just find the first row and
then stop. We are obviously unable to apply this optimization if either
the col = 1 or col2 = 10 were missing from the WHERE clause or if there
were any additional columns in the SELECT clause.
Such queries are probably not all that common, but detecting when we can
apply this optimization amounts to checking if the distinct_pathkeys are
NULL, which is very cheap indeed.
Nothing is done here to check if the query already has a LIMIT clause. If
it does then the plan may end up with 2 Limits nodes. There's no harm in
that and it's probably not worth the complexity to unify them into a
single Limit node.
Author: David Rowley
Reviewed-by: Richard Guo
Discussion: https://postgr.es/m/CAApHDvqS0j8RUWRUSgCAXxOqnYjHUXmKwspRj4GzVfOO25ByHA@mail.gmail.com
Discussion: https://postgr.es/m/MEYPR01MB7101CD5DA0A07C9DE2B74850A4239@MEYPR01MB7101.ausprd01.prod.outlook.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/5543677ec90a15c73dab5ed4f0902b3b920f0b87
Modified Files
--------------
src/backend/optimizer/plan/planner.c | 77 ++++++++++++++++++++----
src/test/regress/expected/select_distinct.out | 54 +++++++++++++++++
src/test/regress/expected/select_distinct_on.out | 50 +++++++++++++++
src/test/regress/sql/select_distinct.sql | 26 ++++++++
src/test/regress/sql/select_distinct_on.sql | 25 ++++++++
5 files changed, 221 insertions(+), 11 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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-committers@postgresql.org
Cc: drowley@postgresql.org
Subject: Re: pgsql: Use Limit instead of Unique to implement DISTINCT, when possible
In-Reply-To: <E1ooMHf-0019kV-QX@gemulon.postgresql.org>
References: <E1ooMHf-0019kV-QX@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