Re: Functionally dependent columns in SELECT DISTINCT

Willow Chargin schrieb am 13.09.2024 um 07:20:
> Hello! Postgres lets us omit columns from a GROUP BY clause if they are
> functionally dependent on a grouped key, which is a nice quality-of-life
> feature. I'm wondering if a similar relaxation could be permitted for
> the SELECT DISTINCT list?
>
> I have a query where I want to find the most recent few items from a
> table that match some complex condition, where the condition involves
> joining other tables. Here's an example, with two approaches:

What about using DISTINCT ON () ?
SELECT DISTINCT ON (items.id) items.*
FROM items
JOIN parts ON items.id = parts.item_id
WHERE part_id % 3 = 0
ORDER BY items.id,items.create_time DESC
LIMIT 5;

This gives me this plan: https://explain.depesz.com/s/QHr6 on 16.2 (Windows, i7-1260P)