public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Geoghegan <[email protected]>
To: Jon Emord <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Poor performance with row wise comparisons
Date: Thu, 9 Oct 2025 17:53:49 -0400
Message-ID: <CAH2-WzmLREy6r68A6SEHXnstg01kNs1HiQtOvSO5cTvWuaducw@mail.gmail.com> (raw)
In-Reply-To: <BLAPR09MB64993673A034EE93C6C6F4B6CCF62@BLAPR09MB6499.namprd09.prod.outlook.com>
References: <BLAPR09MB64993673A034EE93C6C6F4B6CCF62@BLAPR09MB6499.namprd09.prod.outlook.com>
On Fri, Feb 7, 2025 at 2:05 AM Jon Emord <[email protected]> wrote:
> My expectation is that the following two queries would have roughly the same performance.
> They both use the same index only scans and return the same 100 rows of data.
> The main difference I see in the explain output is that the row wise comparison 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) <= 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 = 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.
--
Peter Geoghegan
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Poor performance with row wise comparisons
In-Reply-To: <CAH2-WzmLREy6r68A6SEHXnstg01kNs1HiQtOvSO5cTvWuaducw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox