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
