Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT
От | Dimitrios Apostolou |
---|---|
Тема | Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT |
Дата | |
Msg-id | f13c9e32-8177-c5ab-ef41-5d8137a49da0@gmx.net обсуждение исходный текст |
Ответы |
Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT
Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT |
Список | 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
В списке pgsql-general по дате отправления: