MIME-Version: 1.0
References: 
 <BLAPR09MB64993673A034EE93C6C6F4B6CCF62@BLAPR09MB6499.namprd09.prod.outlook.com>
In-Reply-To: 
 <BLAPR09MB64993673A034EE93C6C6F4B6CCF62@BLAPR09MB6499.namprd09.prod.outlook.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Thu, 9 Oct 2025 17:53:49 -0400
Message-ID: 
 <CAH2-WzmLREy6r68A6SEHXnstg01kNs1HiQtOvSO5cTvWuaducw@mail.gmail.com>
Subject: Re: Poor performance with row wise comparisons
To: Jon Emord <jon@peregrine.io>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-WzmLREy6r68A6SEHXnstg01kNs1HiQtOvSO5cTvWuaducw%40mail.gmail.com>
Precedence: bulk

On Fri, Feb 7, 2025 at 2:05=E2=80=AFAM Jon Emord <jon@peregrine.io> wrote:
> My expectation is that the following two queries would have roughly the s=
ame performance.
> They both use the same index only scans and return the same 100 rows of d=
ata.
> The main difference I see in the explain output is that the row wise comp=
arison has 3,000 times
> the shared buffer hits, but it is unclear why there would need to be more=
 hits.

I agree that this doesn't make sense.

The problem here is that the row compare condition that terminates the
scan (namely "(ROW(data_model_id, primary_key) <=3D ROW(123,
'DEF'::text))") was unable to recognize that we've reached the end of
all matching tuples upon reaching the first tuple that's > "(123,
'DEF')". The scan would only terminate upon reaching the first tuple
whose data_model_id was > 123. Which (in this particular case) meant
that the scan read far more index leaf pages than necessary. Note that
this wouldn't have mattered very much if there weren't so many
irrelevant tuples that were "data_model_id =3D 123 AND > '(123, 'DEF')'"
-- but there were.

I fixed this problem in passing, in the context of a bug fix that went
into Postgres 18 (see commit bd3f59fd, in particular the part about
marking lower-order subkeys as required to continue the scan,
described towards the end of the commit message). You should look into
upgrading to Postgres 18 if this issue is important to you.

--=20
Peter Geoghegan