Re: Poor performance with row wise comparisons - Mailing list pgsql-performance

Looking for help on storing and retrieving the personal data as masked. Any references and implementation details would
help

> On 10 Oct 2025, at 3:24 AM, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Fri, Feb 7, 2025 at 2:05 AM Jon Emord <jon@peregrine.io> 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
>
>

--
________________________________________________________________
Note:
Privileged/Confidential information may be contained in this
message and
may be subject to legal privilege. Access to this e-mail by
anyone other
than the intended is unauthorised. If you are not the
intended recipient
(or responsible for delivery of the message to such
person), you may not
use, copy, distribute or deliver to anyone this
message (or any part of
its contents ) or take any action in reliance on
 it. In such case, you
should destroy this message, and notify us
immediately. If you have
received this email in error, please notify us
immediately by e-mail or
telephone and delete the e-mail from any
computer. The integrity and
security of e-mail communication cannot be guaranteed via the public
Internet as information can be intercepted, corrupted, lost, destroyed,
arrive late or contain viruses. Boson Systems Pvt. Ltd. hereby disclaims
any liability for the correct and complete transmission of information
contained in e-mail messages or for any delay in its receipt. If
verification of the content of any e-mail communication is required, please
request a hard copy version of the e-mail from the original sender. If you
or your employer does not consent to internet e-mail
messages of this
kind, please notify us immediately. All reasonable
precautions have been
taken to ensure no viruses are present in this
e-mail. As our company
cannot accept responsibility for any loss or
damage arising from the use
of this e-mail or attachments we recommend
that you subject these to your
virus checking procedures prior to use.
The views, opinions, conclusions
and other informations expressed in
this electronic mail are not given or
endorsed by the company unless
otherwise indicated by an authorized
representative independent of this
message.