SELECT DISTINCT

Hello Guys,

During my work, I have seen a common practice of using DISTINCT . Some will argue that developer should know the effect of using it, but keep in mind not all developers are gurus in RDBMs. Normally, developers work in a narrow domain. Using DISTINCT might lead to a huge performance  degradation because of sort and filter or hashaggregate operations. I think also the rules in determining if the distinct is requiered or not  is moderate in complexity.

Example: Please see how much extra cost we have for 119 record

EXPLAIN ANALYZE SELECT  DISTINCT * FROM pg_aggregate;

"HashAggregate  (cost=3.98..5.17 rows=119 width=28) (actual time=0.525..0.743 rows=119 loops=1)"
"  ->  Seq Scan on pg_aggregate  (cost=0.00..2.19 rows=119 width=28) (actual time=0.011..0.195 rows=119 loops=1)"
"Total runtime: 1.008 ms"

I think any query that returns a unique column (primary key, unique) which is not duplicated in some way (join) can use this optimisation technique.

EXAMPLE:

TABLE A (a1 (uinque), a2, ... , an)

SELECT DISTINCT a1, subset of (a2...an)  FROM A;   -- will return always a distinct  result.

When it comes to joins and nested queries , I do not have clear idea how this  can be implemented. But I could do some search.

Regards