From: | Dimitrios Apostolou <jimis(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT |
Date: | 2023-11-15 09:57:53 |
Message-ID: | f13c9e32-8177-c5ab-ef41-5d8137a49da0@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Wienhold | 2023-11-15 10:59:20 | Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT |
Previous Message | dld | 2023-11-14 15:49:46 | Re: Indexing fragments of a column's value ? |