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

On 14/3/2024 16:31, Alexander Korotkov wrote:
> On Wed, Mar 13, 2024 at 2:16 PM Andrei Lepikhov 
> As you can see this case is not related to partial indexes.  Just no 
> index selective for the whole query.  However, splitting scan by the OR 
> qual lets use a combination of two selective indexes.
I have rewritten the 0002-* patch according to your concern. A candidate 
and some thoughts are attached.
As I see, we have a problem here: expanding each array and trying to 
apply an element to each index can result in a lengthy planning stage.
Also, an index scan with the SAOP may potentially be more effective than 
with the list of OR clauses.
Originally, the transformation's purpose was to reduce a query's 
complexity and the number of optimization ways to speed up planning and 
(sometimes) execution. Here, we reduce planning complexity only in the 
case of an array size larger than MAX_SAOP_ARRAY_SIZE.
Maybe we can fall back to the previous version of the second patch, 
keeping in mind that someone who wants to get maximum profit from the 
BitmapOr scan of multiple indexes can just disable this optimization, 
enabling deep search of the most optimal scanning way?
As a compromise solution, I propose adding one more option to the 
previous version: if an element doesn't fit any partial index, try to 
cover it with a plain index.
In this case, we still do not guarantee the most optimal fit of elements 
to the set of indexes, but we speed up planning. Does that make sense?

-- 
regards,
Andrei Lepikhov
Postgres Professional