3 ms·
> 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
by paulddraper 7y ago
> 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