Apply the "LIMIT 1" optimization to partial DISTINCT

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Cc: David Rowley <dgrowleyml(at)gmail(dot)com>
Subject: Apply the "LIMIT 1" optimization to partial DISTINCT
Date: 2024-01-26 07:42:33
Message-ID: CAMbWs49JC0qvfUbzs-TVzgMpSSBiMJ_6sN=BaA9iohBgYkr=LA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In 5543677ec9 we introduced an optimization that uses Limit instead of
Unique to implement DISTINCT when all the DISTINCT pathkeys have been
marked as redundant. I happened to notice that this optimization was
not applied to partial DISTINCT, which I think should be. This can
improve plans in some cases, such as

-- on master
explain (costs off) select distinct four from tenk1 where four = 4;
QUERY PLAN
----------------------------------------------
Limit
-> Gather
Workers Planned: 4
-> Unique
-> Parallel Seq Scan on tenk1
Filter: (four = 4)
(6 rows)

-- patched
explain (costs off) select distinct four from tenk1 where four = 4;
QUERY PLAN
----------------------------------------------
Limit
-> Gather
Workers Planned: 4
-> Limit
-> Parallel Seq Scan on tenk1
Filter: (four = 4)
(6 rows)

Such queries might not be that common, but it's very cheap to apply this
optimization.

Attached is a patch for that.

Thanks
Richard

Attachment Content-Type Size
v1-0001-Apply-the-LIMIT-1-optimization-to-partial-DISTINCT.patch application/octet-stream 4.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2024-01-26 07:54:45 Re: A performance issue with Memoize
Previous Message Masahiko Sawada 2024-01-26 07:04:47 Re: Remove unused fields in ReorderBufferTupleBuf