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

On Tue, Dec 5, 2023 at 8:20 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> On Tue, Dec 5, 2023 at 8:06 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> > 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.
>
> Couldn't you condition the use of the optimization on
> _bt_preprocess_keys being able to use cross-type operators when it
> checked for redundant or contradictory scan keys? The vast majority of
> index scans will be able to do that.

Some quick experimentation shows that my test case works as expected
once _bt_preprocess_keys is taught to remember that it has seen a
maybe-unsafe case, which it stashes in a special new field from the
scan's state for later. As I said, this field can be treated as a
condition of applying the required-in-opposite-direction-only
optimization in _bt_readpage().

This new field would be analogous to the existing
requiredMatchedByPrecheck state used by _bt_readpage() to determine
whether it can apply the required-in-same-direction optimization. The
new field works for the whole scan instead of just for one page, and
it works based on information from "behind the scan" instead of
information "just ahead of the scan". But the basic idea is the same.

_bt_preprocess_keys is rather complicated. It is perhaps tempting to
do this in a targeted way, that specifically limits itself to the exact
cases that we know to be unsafe. But it might be okay to just disable
the optimization in most or all cases where _bt_compare_scankey_args()
returns false. That's likely to be very rare in practice, anyway (who
really uses opfamilies like these?). Not really sure where to come
down on that.

--
Peter Geoghegan