6 ms·
Distinct on: a confusing, unique and useful feature in Postgres
- Tostino 7y agoDistinct on is for sure one of the most useful Postgres extensions to SQL, as far as how concise it makes queries which otherwise would suck to write. I would have liked to see equivalent queries written with GROUP BY so people could understand and translate that knowledge even if they don't have direct Postgres experience. Thanks for the article!
- beckingz 7y agoThis would be great, because getting those same results in standard SQL or MariaDB is a pain.
- claytonjy 7y agoI believe the demo query SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC; could also be written with a self-join like so SELECT location, time, report FROM weather_reports JOIN ( SELECT location, max(time) AS time FROM weather_reports GROUP BY location ) as _ USING (location, time) ORDER BY location; though I'd be curious what the query-plan differences are. I suspect the DISTINCT ON might translate more closely to a lateral join SELECT FROM ( SELECT DISTINCT(location) FROM weather_reports ) AS locations JOIN LATERAL ( SELECT location, time, report FROM weather_reports WHERE location = locations.location ORDER BY ts DESC LIMIT 1 ) AS reports ON TRUE ORDER BY location; But I really can't say.
- paulddraper 7y agoThe first only works if location,time is unique. In the example it may be, not necessarily in general. And yes, the JOIN LATERAL would be a good substitute. P.S. More concise is CROSS JOIN ... rather than JOIN ... ON TRUE
- zeroimpl 7y agoIn general if you query a table twice, the query planner will visit that table twice. So DISTINCT ON is almost always more efficient. The main exception I’ve seen is when using it in a view, if you try to filter the results of the view, it usually can’t optimize a DISTINCT ON, but sometimes can for GROUP BY (though that might have improved on newer versions)
- beckingz 7y agoThis removes the self join needed to ask the query "(latest/first/biggest/smallest) record per (customer/user/entity)". Nice!
- caseyohara 7y agoI've been struggling with a similar query recently perhaps better suited for a time series DB. Can DISTINCT ON be used to ask the query "latest record per customer per day". For example, let's say we have a bunch of devices that send sensor readings arbitrarily throughout each day. Could DISTINCT ON be used to query the last reading of each day per device?
- eyelidlessness 7y agoYes, you can `DISTINCT ON` with a `DATE_TRUNC` expression which should get you what you want.
- lenzm 7y agoAbsolutely. You'll need an expression to get the date out of the timestamp, then you want rows that are distinct on customer_id & date order by timestamp desc.
- claytonjy 7y agoI've been using Timescale DB, a postgres extension, to store our sensor-produced timeseries data and it handles this pretty well. DISTINCT ON seems to ruin the query plan there, by forcing sequential, non-index scans of each subtable (timescale breaks tables into time-buckets behind the scenes, so it can skip searching tables in time-filtered queries). I've had good performance with their "time_bucket" and "last" functions, though, and not having to use some NoSQL time series database has been really nice.
- dagss 7y agoAlso consider expressin this with a lateral join. Scroll a bit down here for a similar example. https://stackoverflow.com/questions/11472790/postgres-analogue-to-cross-apply-in-sql-server https://stackoverflow.com/questions/11472790/postgres-analog...
- cosmie 7y agoOne really useful call out is that it supports expressions, not just columns. You can create derived entities via case statements (think binning, entity normalization, etc) and leverage the super handy functionality against those derived entities. It also allows you to use multiple entities/expressions in the distinct on, so you can pivot the logic between granularity levels. Which comes in handy when needing to pull rollups latest/first/biggest/smallest style rollups from varying perspectives.
- rattray 7y agoThis sounds pretty awesome. Anyone have any sample queries demonstrating this lying around?
- cosmie 7y agoI threw together a Fiddle that demonstrates it here[1]. It shows the basic single column version, a multi-column distinct on, and an example that uses a case statement for a derived column. The only thing with the third example is that I don't believe you can alias the derived column within the distinct on field itself, so have to repeat the expression - once within the distinct on and again in the column list of the select statement. [1] https://www.db-fiddle.com/f/7CCYFf14UtkuGYfGZAt5PY/3 https://www.db-fiddle.com/f/7CCYFf14UtkuGYfGZAt5PY/3
- Groxx 7y agoSeems like a saner option (in that behavior is defined) than MySQL's ability to select columns in a group-by that are not aggregates / grouped-on. e.g. this works in MySQL, and has for many years: select a, b, c from table group by a and you'll get undefined (but valid) b and c items from the grouped results per `a`. It's absurdly useful at times - if the non-grouped columns are just denormalized data, it doesn't matter which row they came from, and there's no need to spend time sorting/maxing/etc. And sometimes you truly don't care which entry you get, you just want an entry, e.g. to show a sample of what's in the group.
- greiskul 7y agoIs this similar to BigQuery's ANY_VALUE aggregate function? I found it to be extremely useful.
- traek 7y agoYes, similar to that and Presto’s `ARBITRARY`.
- bfoks 7y agoBut not by default. It's related to ONLY_FULL_GROUP_BY. More here: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.ht...
- Groxx 7y agoHuh. It has been enabled on every system I've touched so far, didn't realize it wasn't on by default. TIL
- Groxx 7y agoIt's entirely possible I've mostly touched 5.6 systems, now that I think about it. Looks like this changed in 5.7(.5 specifically): https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.ht... vs https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.ht...
- cosmotic 7y agoIf you want your application to be portable, don't use this feature.
- irrational 7y agoDo you mean portable as in able to run on different databases? Your application would have to be fairly simple to achieve that goal. We moved an application from Oracle to Postgres. It took about 2 years to complete.
- cosmotic 7y agoIf you start out minimizing proprietary features, its a lot easier to do. Oracle is filled with proprietary features so I'm not surprised it was a lot of work. My application is pretty simple and I converted from mysql to postgres in an afternoon.
- cosmotic 7y agoMany applications are built on ORMs which are pretty portable. About the only thing I regularly depend on that is proprietary per RDBMS is "last insert autoincrement id" since it's not part of the SQL standard. Binary blobs is another big one.
- _jal 7y agoThis advice grew out of the days when database platforms cost enormous amounts of money. It is still as true as it ever was, but since then the biggest pressure that lead to it no longer applies. Back before PG and others were viable competitors to the big commercial databases, about the only leverage customers had against aggressive sales people was the threat to switch to a competitor. So people would preserve that as a credible threat by not using proprietary SQL extensions. No vampiric salesdroid from Postgres is going to knock on your door demanding 8 figures. Now, there may be other reasons you want to switch away from it, or you may want to support a variety of DBs in whatever you're building, or some other reason. So it is good to be aware of. But shying away from SQL extensions is usually no longer a matter of budgetary apocalypse.
- munk-a 7y ago
- Twisell 7y agoWhat's nice about PostgreSQL is that you quite often discover little useful trick you have missed so far. Guess that's however true for any well written language when you master it enough to enjoy theses gems hidden in plain sight.
- abalashov 7y agoDISTINCT ON, when I stumbled upon it rather accidentally a few years ago, was a life saver in some complicated queries in some even more complicated stored functions in our telephony infrastructure product, where only the first route offered by any vendor that otherwise matches a set of criteria is desired. The alternative would have been a lot less concise and would have required a much longer stored procedure.
- thibautg 7y agoDISTINCT ON is also very useful for nearest neighbor queries with PostGIS.