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 <[email protected]> wrote: > > 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 > > -- ________________________________________________________________ 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.
