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

On Wed, Dec 6, 2023 at 6:05 AM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> On Wed, Dec 6, 2023 at 3:46 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> > On Tue, Dec 5, 2023 at 4:41 PM Peter Geoghegan <pg(at)bowt(dot)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.

I dig into the problem. I think this assumption is wrong in my commit.

"When the key is required for opposite direction scan, it must be
already satisfied by_bt_first() ..."

In your example "foo = 90" is satisfied by_bt_first(), but "foo >
99::int8" is not. I think this could be resolved by introducing a
separate flag exactly distinguishing scan keys used for _bt_first().
I'm going to post the patch doing this.

------
Regards,
Alexander Korotkov