Use Limit instead of Unique to implement DISTINCT, when possible

Enterprise / PostgreSQL - David Rowley [postgresql.org] - 28 October 2022 10:04 UTC

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

5543677ec9 Use Limit instead of Unique to implement DISTINCT, when possible
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(-)

Upstream: git.postgresql.org


  • Share