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
could also be written with a self-join like so
though I'd be curious what the query-plan differences are. I suspect the DISTINCT ON might translate more closely to a lateral join
But I really can't say.
[ Parent
| on HN ]
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)