Horton

Re: Making Row Comparison NULL row member handling more robust during skip scans

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-06-27T21:35:50Z
Lists: pgsql-hackers

Attachments

On Wed, Jun 25, 2025 at 5:44 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Correction: there *is* a live bug like this on Postgres 18/HEAD, which
> involves simple scalar inequalities with an incomplete opfamily.
> Attached v3 fixes the bug in its new 0001-* patch.

Attached is v4, which is largely just a polished version of v3. It has
improved comments and more worked out commit messages. Plus the second
patch (the row compare patch) now teaches _bt_first to fully rely on
scan key requiredness markings, just like with other scan keys.

Current plan is to commit these two some time next week. It'd be nice
to get some code review before then, or any kind of input on my
general direction.

Note again that the approach I've taken to fixing the bug *adds a
performance optimization*. It will fix the row compare performance
problem described on a thread from February of this year:

https://www.postgresql.org/message-id/flat/BLAPR09MB64993673A034EE93C6C6F4B6CCF62%40BLAPR09MB6499.namprd09.prod.outlook.com

It will *also* fix the row compare performance problem that Tom
complained about last year, which included its own test case:

https://postgr.es/m/66001.1715455158@sss.pgh.pa.us

I am aware that this all sounds quite odd. Yes, I'm proposing a bug
fix that improves performance in what must seem like fairly tangential
cases. As I went into already, it really did just work out that way.

I had forgotten about the two threads I'm referencing here until I saw
references to each of them in my skip scan project notes. I'm
mentioning them on the thread now in case they're useful breadcrumbs
for somebody down that revisits this thread in the future.

-- 
Peter Geoghegan

Commits

  1. Make row compares robust during nbtree array scans.

  2. Make handling of redundant nbtree keys more robust.

  3. Revert "nbtree: Remove useless row compare arg."

  4. nbtree: Remove useless row compare arg.

  5. Prevent premature nbtree array advancement.

  6. Improvements and fixes for e0b1ee17dc

  7. Fix incorrect handling of NULL index entries in indexed ROW() comparisons.

  8. Fix btree stop-at-nulls logic properly.

  9. Stop btree indexscans upon reaching nulls in either direction.