Re:Distinct on: a confusing, unique and useful feature in Po...
by paulddraper on Friday March 20, 2020 @07:53PM
(#22642961)
> it looks like you the function would be unable to re-use index reads for other column values.
PostgreSQL would have to do a separate sort for each different ORDER BY, yes.
Also, it can't use the the index to scan only some values as it could for DISTINCT ON, because the aggregate is opaque. That may or may not actually matter though.
Another option (though more verbose) is LATERAL plus LIMIT 1.
> Do you have much experience with Postgres WINDOW expressions?
Yes.
SELECT DISTINCT
a,
first_value(b) OVER (PARTITION BY a ORDER BY x),
first_value(c) OVER (PARTITION BY a ORDER BY x DESC)
FROM table
One downside is that I can't use FILTER with those as with aggregates, though sometimes you can hack something together with PARTITION/ORDER BY/RANGE
PostgreSQL would have to do a separate sort for each different ORDER BY, yes.
Also, it can't use the the index to scan only some values as it could for DISTINCT ON, because the aggregate is opaque. That may or may not actually matter though.
Another option (though more verbose) is LATERAL plus LIMIT 1.
> Do you have much experience with Postgres WINDOW expressions?
Yes.
One downside is that I can't use FILTER with those as with aggregates, though sometimes you can hack something together with PARTITION/ORDER BY/RANGE