4 ms·
What is wrong with DISTINCT?
by 0az 6y ago
What is wrong with DISTINCT?
- cultofmetatron 6y agoseriously.. I'm building out some functionality using plpgsql and have used it. This is going to be haunting my dreams
- wesd 6y agothere is possible performance hit [1]. Also, it could mean that the data granularity has not been modeled well if there are duplicate rows. https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct https://sqlperformance.com/2017/01/t-sql-queries/surprises-a...
- marcosdumay 6y agoNearly every time, it's a symptom of bad data normalization. But every time, it interferes badly with any kind of locking (that's DBMS dependent, of course), and imposes a high performance penalty (on every DBMS).
- philshem 6y ago“Think before you DISTINCT”
- barbegal 6y agoDISTINCT generally requires the results to be sorted which has O(n^2) worst performance so it can have a big performance hit on a query. It is best to make your database structure such that queries only return distinct data. E.g. by disallowing duplicates
- namibj 6y agoIf your sorting algorithm degrades to anywhere near O(n^2) in pathological cases, you're doing something wrong. And even if it's just a kind of timeout/operations-limit to detect pathological cases and just run an in-place mergesort instead. Tail latency/containing pathological data is quite important if there's any interactivity.
- barrkel 6y agoIn order to determine the distinct items, the items need to be deduplicated. Generally that's done in only two ways: a hash table that skips items already seen, or a sort followed by a scan that skips over duplicates. The hash table is O(1), but the sort is easier to make parallel without sharing mutable state and has more established algorithms to use when spilling to disk.
- branko_d 6y agoThere is a third way: keep the data pre-sorted in the database (via an index).
- GlennS 6y agoIt covers up bad queries, so you may not see an underlying data duplication problem. Often better to group explicitly so you know what's actually going on.
- matwood 6y ago> It covers up bad queries, Bingo. I used to work with a guy who would see duplicate results and just throw a distinct on his query. I had to keep on him to fix his queries or explain why distinct was correct in this case. My default is that distinct is almost always not the solution.