Thread

Postgres SELECT DISTINCT Does Not Scale

103 points · 28 comments · KraftyOne

Loading the complete thread in the background. This saved snapshot is available now. Refresh

DiabloD3 · 1790390529 · focus · HN ↗
"Postgres SELECT DISTINCT Does Not Scale"
Correct. This is documented in depth: DISTINCT sorts the results first.
The article's use case seems to imply the author did not know about GROUP BY, nor does it imply the author knew about indexes, nor ANALYZE. Postgres 18's new skip scan indexing also could help here, so ensuring the planner chooses that could help.
1. Dylan16807 · 1790390841 · focus · HN ↗
  Would GROUP BY fix the issue?
  The article explains that skip scan doesn't do anything here.
  > nor does it imply the author knew about indexes, nor ANALYZE
  Indexes were talked about a lot, and they explicitly mentioned looking at the query plan.
  1. gfody · 1790401749 · focus · HN ↗
    nope - either gets you a HashAggregate, GroupAggregate, or Unique depending on whether the column/input is indexed/ordered
  2. DiabloD3 · 1790402404 · focus · HN ↗
    The article seems to have changed since I commented.
    Twisell · 1790403478 · focus · HN ↗
    Why don't he use GROUP BY on indexed columns was my first thought also.
    I guess we just had to patiently wait for the OP to hopefully read Postgres documentation from postgreSQL 10.x before correcting the article again.
    Dylan16807 · 1790404130 · focus · HN ↗
    Does that fix it? Why would people be trying to add new index scanning modes if that's enough to fix it?
    SkiFire13 · 1790416319 · focus · HN ↗
    > Why don't he use GROUP BY on indexed columns was my first thought also.
    Because it has the same issue.
    SELECT DISTINCT and GROUP BY are equivalent from a query planner perspective.
    Dylan16807 · 1790403927 · focus · HN ↗
    I was reading the article within a couple minutes of your first comment and it had those things.
2. tpetry · 1790396172 · focus · HN ↗
  Did you even read the article? They show that a perfect index for their query didnt help because Skip Scan is currently not used for DISTINCT queries.
  1. DiabloD3 · 1790402393 · focus · HN ↗
    The article seems to have changed since I commented.
    joinHNtheysaid · 1790403489 · focus · HN ↗
    [dead]
nattaylor · 1790392234 · focus · HN ↗
Loose index scan is made for this <a href="https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html#loose-index-scan" rel="nofollow">https://dev.mysql.com/doc/refman/8.0/en/group-by-optimizatio...
Postgres doesn't have it yet <a href="https://wiki.postgresql.org/wiki/Loose_indexscan" rel="nofollow">https://wiki.postgresql.org/wiki/Loose_indexscan
1. supermatt · 1790401037 · focus · HN ↗
  As mentioned in the article.
2. Tepix · 1790402984 · focus · HN ↗
  As is mentioned in the article (now).
  1. Dylan16807 · 1790404610 · focus · HN ↗
    (since it was originally posted)
3. nikolatt · 1790416978 · focus · HN ↗
  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: <a href="https://www.tigerdata.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql" rel="nofollow">https://www.tigerdata.com/blog/how-we-made-distinct-queries-....
thesuperevil · 1790402148 · focus · HN ↗
[dead]
sandeepkd · 1790403258 · focus · HN ↗
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.
1. Dylan16807 · 1790404394 · focus · HN ↗
  Scale with what? If you have m distinct values in an index, then listing them this way takes m log(n) time, which is fine for many use cases no matter how much data you have.
  1. sandeepkd · 1790404991 · focus · HN ↗
    The way the OP is trying to achieve all the goals by pushing the complexity on the queries/database is what I am referring to as non-scalable as data grows on SQL DB.
    > If you have m distinct values in an index, then listing them this way takes m log(n) time, which is fine for many use cases no matter how much data you have.
    And NO the runtimes are not right away applicable on machines at scale. You are dealing with DB locks, page sizes, available memory, existing data in memory, queue depth. Experienced folks get paid to short circuit such learnings
    adrianN · 1790406167 · focus · HN ↗
    Runtimes are usually pretty well applicable at scale, it’s just that most people don’t have a good intuition about asymptomatic notation. Constants and lower order terms matter a lot in practice but are hidden in asymptotic notation.
    Dylan16807 · 1790406831 · focus · HN ↗
    Selecting distinct values of a single column with a simple condition is hardly pushing complexity into the database.
2. stemchar · 1790404577 · focus · HN ↗
  It's Stonebraker, he has a history of doing this to sell shit to people who don't need it.
3. atombender · 1790419120 · focus · HN ↗
  My understanding (possibly flawed) is that Stonebraker isn't directly involved anymore.
  The original idea that he worked on with the DBOS people at MIT and Stanford was very different and much, much more ambitious, which is why the name DBOS seems a little out of place now. The original idea was much closer to a "database OS".
  Here [1] is the paper, which proposes that "all operating system state should be represented uniformly as database tables, and operations on this state should be made via queries from otherwise stateless tasks." The team later published another paper based on their prototype work [2].
  Instead, they basically implemented Temporal as a client library with Postgres as the state layer. It's good, but only tangentially related to the original vision.
  Maybe the long-term plan is an actual database OS, but it kind of looks like they decided they had to pivot to something much simpler, and slapped on an "for AI" like everyone is doing these days.
  [1] <a href="https://arxiv.org/abs/2007.11112" rel="nofollow">https://arxiv.org/abs/2007.11112
  [2] <a href="https://dl.acm.org/doi/10.14778/3485450.3485454" rel="nofollow">https://dl.acm.org/doi/10.14778/3485450.3485454
procaryote · 1790406674 · focus · HN ↗
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
1. Dylan16807 · 1790406988 · focus · HN ↗
  It's definitely a warning flag if you're applying it to full-ish rows. This situation seems much more innocuous to me.
2. photios · 1790410248 · focus · HN ↗
  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
3. thom · 1790417515 · focus · HN ↗
  Yeah, it's almost always more intention-revealing to use CTEs and WHERE EXISTS.
4. wvbdmp · 1790419714 · focus · HN ↗
  As always, it’s worth distinguishing use cases. I use DISTINCT all the time in ad-hoc reporting queries and even in longer-lived reports if it doesn’t tank the query’s performance.
  I wouldn’t use it in application code where I control the schema, but it’s great for exploratory stuff and custom reports over third-party databases.
5. gregw2 · 1790431047 · focus · HN ↗
  I agree 100% with each and every word in all six of your sentences.
  I'd only add one more point. Sometimes the root cause is poor table design (or for analytic use cases ETL design without proper preconditions or postconditions) 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. Otherwise the SELECT DISTINCT disease in the code base on top of the database just spreads.
colenikol2 · 1790407919 · focus · HN ↗
Have watched the movie. One of the most addictive games ever. Still playing sometimes. But here is one that can play against your friend on a single phone where both players holds and play on a same phone faced against each other
atemerev · 1790413286 · focus · HN ↗
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.
1. SkiFire13 · 1790417412 · focus · HN ↗
  The manual workaround is even documented in postgres' wiki <a href="https://wiki.postgresql.org/wiki/Loose_indexscan" rel="nofollow">https://wiki.postgresql.org/wiki/Loose_indexscan
martinlucas1994 · 1790602427 · focus · HN ↗
[dead]

Open on Hacker News to reply ↗

Unofficial Hacker News client; not affiliated with Y Combinator.