Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT

On Wed, 15 Nov 2023, Erik Wienhold wrote:

> On 2023-11-15 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?
>
> As Tom Lane recently wrote[1] EXCEPT is not optimized and will operate
> on the subqueries which are planned independently.
>
> [1] https://www.postgresql.org/message-id/2664450.1698799927@sss.pgh.pa.us

Heh, as he wrote to me even. :-) I just wanted to make sure that this is
indeed a missing optimisation of the planner, and that the queries are
effectively the same. Thank you for clarifying.

As mentioned, the docs don't make it clear if the SELECT DISTINCT part is
implied or not, only the EXCEPT DISTINCT part is clearly on by default.


Dimitris