From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jon Emord <jon@peregrine.io>
cc: Greg Sabino Mullane <htamfids@gmail.com>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Poor performance with row wise comparisons
In-reply-to: 
 <BLAPR09MB64998C1078D3E84803E10BEECCF12@BLAPR09MB6499.namprd09.prod.outlook.com>
References: 
 <BLAPR09MB64993673A034EE93C6C6F4B6CCF62@BLAPR09MB6499.namprd09.prod.outlook.com>
 <CAKAnmmJDHajWWYx8bMUJRxVHZnLnP0WhEMZD40jeqrZ7aLsFUw@mail.gmail.com>
 <BLAPR09MB64998C1078D3E84803E10BEECCF12@BLAPR09MB6499.namprd09.prod.outlook.com>
Comments: In-reply-to Jon Emord <jon@peregrine.io>
	message dated "Fri, 07 Feb 2025 16:16:27 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2065759.1739140833.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 09 Feb 2025 17:40:33 -0500
Message-ID: <2065760.1739140833@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2065760.1739140833%40sss.pgh.pa.us>
Precedence: bulk

Jon Emord <jon@peregrine.io> writes:
>    ->  Index Only Scan using entity_data_model_id_primary_key_uniq on en=
tity  (cost=3D0.70..873753.60 rows=3D15581254 width=3D31) (actual time=3D0=
.093..2712.836 rows=3D100 loops=3D1)
>          Index Cond: ((ROW(data_model_id, primary_key) >=3D ROW(123, 'AB=
C'::text)) AND (ROW(data_model_id, primary_key) <=3D ROW(123, 'DEF'::text)=
))
>          Heap Fetches: 4
>          Buffers: shared hit=3D97259

>   2.
> data_model_id =3D 123 is the 15 most common value of data_model_id with =
10.8 million records

Hm.  I think your answer is in this comment in nbtree's
key-preprocessing logic:

 * Row comparison keys are currently also treated without any smarts:
 * we just transfer them into the preprocessed array without any
 * editorialization.  We can treat them the same as an ordinary inequality
 * comparison on the row's first index column, for the purposes of the log=
ic
 * about required keys.

That is, for the purposes of deciding when the index scan can stop,
the "<=3D ROW" condition acts like "data_model_id <=3D 123".  So it will
run through all of the data_model_id =3D 123 entries before stopping.

			regards, tom lane