Thread: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT
Hello list,
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?
Reading the docs, I understand that postgres does by default
EXCEPT DISTINCT, but I don't see the same for the 1st clause,
SELECT [DISTINCT].
Steps to reproduce:
CREATE TABLE t1(i INTEGER NOT NULL);
CREATE TABLE t2(i INTEGER NOT NULL);
INSERT INTO t1 VALUES (1),(2),(1),(2),(3),(3),(3);
INSERT INTO t2 VALUES (3);
SELECT * FROM t1 EXCEPT SELECT * FROM t2;
i
---
2
1
(2 rows)
SELECT DISTINCT * FROM t1 EXCEPT SELECT * FROM t2;
i
---
2
1
(2 rows)
EXPLAIN SELECT * FROM t1 EXCEPT SELECT * FROM t2;
QUERY PLAN
---------------------------------------------------------------------------------
HashSetOp Except (cost=0.00..160.25 rows=200 width=8)
-> Append (cost=0.00..147.50 rows=5100 width=8)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..61.00 rows=2550 width=8)
-> Seq Scan on t1 (cost=0.00..35.50 rows=2550 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..61.00 rows=2550 width=8)
-> Seq Scan on t2 (cost=0.00..35.50 rows=2550 width=4)
(6 rows)
EXPLAIN SELECT DISTINCT * FROM t1 EXCEPT SELECT * FROM t2;
QUERY PLAN
---------------------------------------------------------------------------------
HashSetOp Except (cost=41.88..127.50 rows=200 width=8)
-> Append (cost=41.88..120.62 rows=2750 width=8)
-> Subquery Scan on "*SELECT* 1" (cost=41.88..45.88 rows=200 width=8)
-> HashAggregate (cost=41.88..43.88 rows=200 width=4)
Group Key: t1.i
-> Seq Scan on t1 (cost=0.00..35.50 rows=2550 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..61.00 rows=2550 width=8)
-> Seq Scan on t2 (cost=0.00..35.50 rows=2550 width=4)
(8 rows)
Regards,
Dimitris
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 -- Erik
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
On 2023-11-15 12:12 +0100, Dimitrios Apostolou wrote: > 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. SELECT ALL is the default as spelled out in [1]. DISTINCT as the default for UNION/EXCEPT/INTERSECT makes sense because those are set operators. I guess SELECT ALL is the default because SQL allows duplicate rows (contrary to the relation model) and the user should instead be explicit about wanting distinct rows which requires additional computation. But when combining subqueries with the default UNION/EXCEPT/INTERSECT you effectively get SELECT DISTINCT ... UNION SELECT DISTINCT ... when it comes to the result. [1] https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT -- Erik
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