Postgres SELECT DISTINCT Does Not Scale
dbos.dev
dbos.dev
Postgres doesn't have it yet https://wiki.postgresql.org/wiki/Loose_indexscan
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
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.
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.
> 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
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 "To improve the scalability, security and operability of OSes, we propose a data-centric architecture: designing the OS to explicitly separate data from computation, and centralize all state in the OS into a uniform data model. In particular, we propose using database tables, a simple data model that has been used and optimized for decades, to represent OS state. With the data-centric approach, the process table, scheduler state, flow tables, permissions tables, etc all become database tables in the OS kernel, allowing the system to offer a uniform interface for querying this state."
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.
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.
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.
I guess we just have to patiently wait for the OP to hopefully read Postgres documentation from postgreSQL 10.x before correcting the article again.
Someone else said it doesn't.
Because it has the same issue.
SELECT DISTINCT and GROUP BY are equivalent from a query planner perspective.
It had the same stuff a month ago.