MIME-Version: 1.0
References: <CAALRJs5ne=gPYG=FdeY-G0p9QyjXHhPhAyAsJ_evdCwG3vuhug@mail.gmail.com>
 <88e52d11-6185-473d-9eb2-673b62cb2426@gmx.net> <CAALRJs5QkE-yWVdxQxOLXbM8DyPVV3M_=zfsPK17SV-zHQsASQ@mail.gmail.com>
 <CAKFQuwYEtU7doien_XGh58vjRdsJezXp2BQvKdLcyVZQwhLmgg@mail.gmail.com>
In-Reply-To: <CAKFQuwYEtU7doien_XGh58vjRdsJezXp2BQvKdLcyVZQwhLmgg@mail.gmail.com>
From: Willow Chargin <postgresql@wchargin.com>
Date: Fri, 13 Sep 2024 09:12:12 -0700
Message-ID: <CAALRJs7PVbctiMzb9g5NrLET-jPEZpjToY1F-w8CmGbWid5J7g@mail.gmail.com>
Subject: Re: Functionally dependent columns in SELECT DISTINCT
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "shammat@gmx.net" <shammat@gmx.net>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAALRJs7PVbctiMzb9g5NrLET-jPEZpjToY1F-w8CmGbWid5J7g%40mail.gmail.com>
Precedence: bulk

Thanks both for your suggestions so far.

On Fri, Sep 13, 2024 at 8:43=E2=80=AFAM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Friday, September 13, 2024, Willow Chargin <postgresql@wchargin.com> w=
rote:
>>
>> 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=3D1

Hmm, like this? noting that it's rank<=3D5, not rank=3D1:

    -- 1. rank all item-part combinations, densely since an item may
          have multiple parts
    -- 2. limit by rank, still retaining multiple copies of each item
    -- 3. de-duplicate IDs
    SELECT DISTINCT id FROM (
        SELECT id, dense_rank FROM (
            SELECT
                items.id,
                dense_rank() OVER (ORDER BY create_time DESC)
            FROM items JOIN parts ON items.id =3D parts.item_id
            WHERE part_id % 3 =3D 0
        ) q
        WHERE dense_rank <=3D 5
    ) q

I've done this before, but my experience is that it's usually far slower
because the rank is computed eagerly even for rows that don't match the
rank bound. And indeed here it takes 20% longer than even the slower
GROUP BY from before: https://explain.depesz.com/s/mQIi

> or use a lateral subquery to surgically (fetch first 1) retrieve the firs=
t row when sorted by recency descending.

I'm not sure that I see how to apply this when I need top-k, not top-1.