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

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

Responses

Browse pgsql-general by date

  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 ?