Re: POC, WIP: OR-clause support for indexes - Mailing list pgsql-hackers

From Matthias van de Meent
Subject Re: POC, WIP: OR-clause support for indexes
Date
Msg-id CAEze2WiXm3VV-FLbBF2ViqHZarFgD2ufbS0VgwFuzjRLXRiemg@mail.gmail.com
Whole thread Raw
In response to Re: POC, WIP: OR-clause support for indexes  (Peter Geoghegan <pg@bowt.ie>)
List pgsql-hackers


On Mon, 27 Nov 2023, 23:16 Peter Geoghegan, <pg@bowt.ie> wrote:
On Mon, Nov 27, 2023 at 1:04 PM Robert Haas <robertmhaas@gmail.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

pgsql-hackers by date:

Previous
From: "Tristan Partin"
Date:
Subject: Re: SSL tests fail on OpenSSL v3.2.0
Next
From: Robert Haas
Date:
Subject: Re: POC, WIP: OR-clause support for indexes