Horton

Re: Bug in nbtree optimization to skip > operator comparisons (or < comparisons in backwards scans)

Alexander Korotkov <aekorotkov@gmail.com>

View thread View on postgr.es
From: Alexander Korotkov <aekorotkov@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>, Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: 2023-12-06T04:05:52Z
Lists: pgsql-hackers 
Hi, Peter!

On Wed, Dec 6, 2023 at 3:46 AM Peter Geoghegan <pg@bowt.ie> wrote:
> On Tue, Dec 5, 2023 at 4:41 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > "In general, when inequality keys are present, the initial-positioning
> > code only promises to position before the first possible match, not
> > exactly at the first match, for a forward scan; or after the last
> > match for a backward scan."
> >
> > My test case mostly just demonstrates how to reproduce the scenario
> > described by this sentence.
>
> I just realized that my test case wasn't quite minimized correctly. It
> depended on a custom function that was no longer created.
>
> Attached is a revised version that uses btint84cmp instead.

Thank you for raising this issue.  Preprocessing of btree scan keys is
normally removing the redundant scan keys.  However, redundant scan
keys aren't removed when they have arguments of different types.
Please give me a bit of time to figure out how to workaround this.

------
Regards,
Alexander Korotkov

Commits

  1. Fix an oversight in refactoring in 06b10f80ba4.

  2. Make RangeTblEntry dump order consistent

  3. Improvements and fixes for e0b1ee17dc

  4. Remove BTScanOpaqueData.firstPage

  5. Optimize nbtree backward scan boundary cases.

  6. Skip checking of scan keys required for directional scan in B-tree