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 по дате отправления:

Предыдущее
От: dld
Дата:
Сообщение: Re: Indexing fragments of a column's value ?
Следующее
От: Erik Wienhold
Дата:
Сообщение: Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT