3 ms·

Distinct 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

Distinct 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 ago
This would be great, because getting those same results in standard SQL or MariaDB is a pain.
claytonjy 7y ago
I 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 ago
  The 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 ago
  In 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 ago

This would be great, because getting those same results in standard SQL or MariaDB is a pain.

claytonjy 7y ago

I 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 ago
The 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 ago
In 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)