Re:Distinct on: a confusing, unique and useful feature in Po...
by claytonjy on Friday March 20, 2020 @03:06PM
(#22640539)
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.
Re:Distinct on: a confusing, unique and useful feature in Po...
by paulddraper on Friday March 20, 2020 @07:59PM
(#22642995)
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
Re:Distinct on: a confusing, unique and useful feature in Po...
by zeroimpl on Friday March 20, 2020 @09:52PM
(#22643560)
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)
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!