Re: Inefficient nbtree behavior with row-comparison quals

On Sat, May 11, 2024 at 4:21 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > The dependency is fairly simple. In the presence of multiple arrays on
> > the same column, which must be contradictory/redundant, but cannot be
> > simplified solely due to lack of suitable cross-type support, we have
> > multiple arrays on the same index column. _bt_advance_array_keys wants
> > to deal with this by assuming that the scan key order matches the
> > array key order.
>
> I guess what is not clear to me is what you mean by "array key order".
> Is that simply the order of entries in BTArrayKeyInfo[], or are there
> additional assumptions/restrictions?

I simply mean the order of the entries in BTArrayKeyInfo[].

> > There's another problem along these lines, that seems at least as bad:
> > queries involving contradictory >= and <= quals aren't recognized as
> > contradictory during preprocessing. There's no reason why
> > _bt_preprocessing_keys couldn't detect that case; it just doesn't
> > right now.
>
> Ugh, how'd we miss that? I can take a look at this, unless you're
> on it already.

My draft skip scan/MDAM patch already deals with this in passing. So
you could say that I was already working on this. But I'm not sure
that I would actually say so myself; what I'm doing is tied to far
more complicated work.

I haven't attempted to write the kind of targeted fix that you're
thinking of. It might still be worth writing such a fix now. I
certainly have no objections.

--
Peter Geoghegan