| From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
|---|---|
| To: | Dimitrios Apostolou <jimis(at)gmx(dot)net>, pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT |
| Date: | 2023-11-15 18:15:18 |
| Message-ID: | f4bd33ed0872a3b96d4cd27a2ac14b78eaa7e770.camel@cybertec.at |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Wed, 2023-11-15 at 10:57 +0100, Dimitrios Apostolou wrote:
> SELECT [DISTINCT] ... EXCEPT ...
>
> In this query I get the same results regardless of including DISTINCT or
> not. But I get different query plans, I get an extra HashAggregate node
> in the case of SELECT DISTINCT. Any idea why?
The DISTINCT is superfluous, because EXCEPT already removes duplicate rows.
However, the planner does not invest extra processing cycles to detect
that you wrote a superfluous DISTINCT, and it does not remove it.
As a consequence, you end up with a pointless extra execution plan node
that does not achieve anything except slowing down the query.
Remove the DISTINCT.
Yours,
Laurenz Albe
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Eric Wong | 2023-11-16 06:49:39 | db size difference on primary and standby |
| Previous Message | Erik Wienhold | 2023-11-15 12:10:07 | Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT |