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.
