Message-ID: <88e52d11-6185-473d-9eb2-673b62cb2426@gmx.net>
Date: Fri, 13 Sep 2024 08:13:25 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Functionally dependent columns in SELECT DISTINCT
To: pgsql-general@lists.postgresql.org
References: <CAALRJs5ne=gPYG=FdeY-G0p9QyjXHhPhAyAsJ_evdCwG3vuhug@mail.gmail.com>
Content-Language: de-DE
From: shammat@gmx.net
In-Reply-To: <CAALRJs5ne=gPYG=FdeY-G0p9QyjXHhPhAyAsJ_evdCwG3vuhug@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:RM6tdvqsa0A=;qlcqcpYfcyYMYOsBgHRuD6UuMyR
 OqO6sQ6G1HUU60hMip1qrOwjBgaPv6ygUi97vk87ZquPC2kFJAYJIObA4gRmUfN6QTcdqUpBt
 ZrNFhHvCAagP/1NIWiaZU49pTdXAHTyhMIwhpXFZf4rlmgQ/+rIq5cnXxklEMQN7U0T9WXRCQ
 ean4dqJAKe8ekStPFfuhrsUucd7VAg+RzjaUaPpgax2dedovdEsuqYDE0+pAa7/6DBWhLRS0y
 1qRji3irNgSkQHmrgro7W8PSkaJyCTBi8I/kCfp0tyT0JnZu7/NMUIC68gxO04bBXA/6DI3Ei
 Fm1tMXiGHZYRPOkWeHdzw+mrxzWX4/myzm0+WMKR0U2JJ5UEZMS3Vy3lRqQ4c76eUvLe2zvF5
 0Cn3QInG8KPJWf4vZ+bTUauILUSQwROdkQg5/l5lwWWTq85DVSeERRmtAsN9qYZiNkpripder
 usqP30K1z6RkTZbe84eFYm6e2whvSw181FUEcA6GGIs63cJYv6rnFVwObGOhpukLHBsJeMzLj
 V6+aHKA4ipumHiHlz+9LfCayEI0bsEUEMjzkwr7EG+CpjSUf33ZGI2nP3nYCtqPSxV3Di+Q6x
 MjK1GXJ9OnbeoNP4a7RVsOL8LAIipIc7JQTnvDSXYA38JAMFxMAjkWNP22O7grzpH+PGFyC5G
 WFqLbQnducORkD33455xF9IkKmsmxJLwKQzl0qu0gEhTphCnYLj9jRDpp5mUgsJxV4k+qwdBE
 J/Hi1zdjpxkb5OD6QSMyq6JRa8NygQTNALHpOLsQ46b/QI3zXOxatePIrw2fX/hm6O2EHxPmT
 jk86u36/IDwjLutZpg3M6hCA==
Archived-At: <https://www.postgresql.org/message-id/88e52d11-6185-473d-9eb2-673b62cb2426%40gmx.net>
Precedence: bulk

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 =3D parts.item_id
    WHERE part_id % 3 =3D 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  (Windo=
ws, i7-1260P)