Postgres SELECT DISTINCT Scans Every Row, No Matter How You Index It
Postgres SELECT DISTINCT Does Not Scale
Peter Kraft of DBOS describes a Postgres performance trap: SELECT DISTINCT always scans every row matching its predicates, even with a perfect index on queue name, status, and partition key. Diagnosing a partitioned queue workload, the team found the simplest-looking query was the most expensive. The post explains why Postgres makes this choice and how to work around it.
No matter how you index your table, no matter how few unique values there are to retrieve, SELECT DISTINCT will always scan every row that matches its predicates.
- nattaylor
Loose index scan is made for this https://dev.mysql.com/doc/refman/8.0/en/group-by-optimizatio...
Postgres doesn't have it yet https://wiki.postgresql.org/wiki/Loose_indexscan
- procaryote
I've generally started to treat use of SELECT DISTINCT as a warning flag, as it's very common that it indicates bad code
Some use it because they don't understand uniqueness constraints and try to fix it in post so to say. Some use it because they forgot a join condition and are absolute amateurs. Some use it because it fixed a problem for them once and now they add it everywhere
These people seem to outnumber the people who use SELECT DISTINCT in a well thought out manner
- sandeepkd
It says that the company is co-founded by Postgres creator. I find that bit hard to believe given that there is nothing novel in the article, probably discovery for them. I do understand that everyone has to go through their own journey to learn these things but at the same time when you are running business then seeking professional help isnt a bad idea.
Based on my experience queries like these cannot scale, whatever you do. However if you are already on a path where you had invested a lot in such queries then hire a DBA, if you are not far off then hire an architect to model the data for better performance.