Subject: Re: Functionally dependent columns in SELECT DISTINCT

From: [email-masked]
Date: Fri, 13 Sep 2024 08:13:25 +0200

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)

---

From: Willow Chargin <[email-masked]>
Date: Fri, 13 Sep 2024 08:26:53 -0700

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

Ordering by items.id changes the answer, though. In the example I gave,
items.id and items.create_time happened to be in the same order, but
that needn't hold. In reality I really do want the ID columns of the
*most recent* items.

You can see the difference if you build the test dataset a bit
differently:

    INSERT INTO items(id, create_time)
        SELECT i, now() - make_interval(secs => random() * 1e6)
        FROM generate_series(1, 1000000) s(i);

We want the returned create_times to be all recent, and the IDs now
should look roughly random.

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 13 Sep 2024 08:43:54 -0700

On Friday, September 13, 2024, Willow Chargin <[email-masked]>
wrote:

> In reality I really do want the ID columns of the
> *most recent* items.
>

Use a window function to rank them and pull out rank=1, or use a lateral
subquery to surgically (fetch first 1) retrieve the first row when sorted
by recency descending.

David J.