HN
login
Postgres SELECT DISTINCT Does Not Scaledbos.dev

Postgres SELECT DISTINCT performs surprisingly poorly. We explain why and how we mitigated it.

106 points•KraftyOne•6 days ago•28 comments•
#select#distinct#postgres#postgres select#select distinct#postgres select distinct#scale#performs

28 comments

nattaylor5 days ago
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

nikolatt5 days ago
I haven't followed the latest Postgres releases, but I thought it would be added by now - Timescale/Tigerdata have already implemented a similar index scan in Postgres: https://www.tigerdata.com/blog/how-we-made-distinct-queries-....
Tepix5 days ago
As is mentioned in the article (now).
Dylan168075 days ago
(since it was originally posted)
procaryote5 days ago
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

gregw25 days ago
This. 100%. So on point.

I'd only add one more observation.

Sometimes the root cause is poor table design (or in analytic/OLAP use cases poor ETL design without proper data validation checks or handling) where uniqueness is not enforced and that is the root cause that should be fixed if at all possible. A "first normal form" violation in the database design so to speak.

If that root cause is not addressed, then SELECT DISTINCT is more often necessary and the SELECT DISTINCT disease to be safe culture and behavior in the code base on top of the database just spreads.

photios5 days ago
Yeah, people have been advising against DISTINCT for ages. I guess that piece of common wisdom somehow got lost in this age of AI wonders. :D
thom5 days ago
Yeah, it's almost always more intention-revealing to use CTEs and WHERE EXISTS.
Dylan168075 days ago
It's definitely a warning flag if you're applying it to full-ish rows. This situation seems much more innocuous to me.
atemerev5 days ago
Well, if the solution is a manual workaround that forces a better query plan, this needs to be a part of Postgres itself so it builds better query plans automatically.
SkiFire135 days ago
The manual workaround is even documented in postgres' wiki https://wiki.postgresql.org/wiki/Loose_indexscan

Read the full thread on Hacker News →

Related stories