Re: POC, WIP: OR-clause support for indexes

On Mon, 27 Nov 2023, 23:16 Peter Geoghegan, <pg(at)bowt(dot)ie> wrote:

> On Mon, Nov 27, 2023 at 1:04 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > The use of op_mergejoinable() seems pretty random to me. Why should we
> > care about that? If somebody writes a<1 or a<2 or a<3 or a<4, you can
> > transform that to a<any(array[1,2,3,4]) if you want. It might not be a
> > good idea, but I think it's a legal transformation.
>
> That kind of transformation is likely to be a very good idea, because
> nbtree's _bt_preprocess_array_keys() function knows how to perform
> preprocessing that makes the final index qual "a < 1". Obviously that
> could be far more efficient.
>

a < 4, you mean? The example mentioned ANY, not ALL

Further suppose you have a machine generated query "a<1 or a<2 or a<3
> or a<4 AND a = 2" -- same as before, except that I added "AND a = 2"
> to the end. Now _bt_preprocess_array_keys() will be able to do the
> aforementioned inequality preprocessing, just as before. But this time
> _bt_preprocess_keys() (a different function with a similar name) can
> see that the quals are contradictory. That makes the entire index scan
> end, before it ever really began.
>

With the given WHERE-clause I would hope it did *not* return before
scanning the index, given that any row with a < 3 is valid for that
constraint with current rules of operator precedence.

- Matthias