News for hackers. Stuff that matters.
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)