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@xxxxxxxxxxxxx
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
