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
