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

On Thu, Oct 3, 2024 at 11:31 PM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> The origin reason was - to avoid multiple BitmapOr, which has some
> effects at the planning stage (memory consumption, planning time) and
> execution (execution time growth). IndexScan also works better with a
> single array (especially a hashed one) than with a long list of clauses.

I understand that that was the original goal. But I think that
preserving ordered index scans by using a SAOP (not filter quals and
not a BitmapOr) is actually the more important reason to have this
patch. It allows the OR syntax to be used in a way that preserves
crucial context.

I'm not really trying to add new requirements for this patch. The case
I highlighted wasn't a particularly tricky one. It's a case that the
existing IN() syntax somehow manages to produce a useful SAOP for. It
would be nice to get that part right.

> Another reason is that by spending some time identifying common operator
> family and variable-side clause equality, we open a way for future cheap
> improvements like removing duplicated constants.

I don't think that removing duplicated constants is all that
important, since we already do that during execution proper. The
nbtree code does this in _bt_preprocess_array_keys. It even does
things like merge together a pair of duplicate SAOPs against the same
column. It doesn't matter if the arrays are of different types,
either.

It doesn't look like index AMs lacking native support for SAOPs can do
stuff like that right now. It could be implemented by sorting and
deduplicating the IndexArrayKeyInfo.elem_values[] array in the same
way as nbtree.

--
Peter Geoghegan