From: lokesh@empays.com
Mime-Version: 1.0 (1.0)
Subject: Re: Poor performance with row wise comparisons
Message-Id: <5EBECECB-EDF2-4309-B985-680D75E8FE17@empays.com>
References: 
 <CAH2-WzmLREy6r68A6SEHXnstg01kNs1HiQtOvSO5cTvWuaducw@mail.gmail.com>
In-Reply-To: 
 <CAH2-WzmLREy6r68A6SEHXnstg01kNs1HiQtOvSO5cTvWuaducw@mail.gmail.com>
To: pgsql-performance@lists.postgresql.org
Date: Fri, 10 Oct 2025 09:51:01 +0530
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/5EBECECB-EDF2-4309-B985-680D75E8FE17%40empays.com>
Precedence: bulk

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=E2=80=AFAM, Peter Geoghegan <pg@bowt.ie> wrote:
>=20
> =EF=BB=BFOn 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 =
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 com=
parison has 3,000 times
>> the shared buffer hits, but it is unclear why there would need to be mor=
e hits.
>=20
> I agree that this doesn't make sense.
>=20
> 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.
>=20
> 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
>=20
>=20

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