| 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: | Whole Thread | Raw Message | 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 | 
| 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 |