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

On Mon, Jul 29, 2024 at 5:36 AM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> On Sun, Jul 28, 2024 at 12:59 PM Alena Rybakina
> > Because of these reasons, I tried to save this and that transformation
> > together for each column and try to analyze for each expr separately
> > which method would be optimal.
>
> Yes, with v27 of the patch, optimization wouldn't work in these cases.
> However, you are using quite small table. If you will use larger
> table or disable sequential scans, there would be bitmap plans to
> handle these queries. So, v27 doesn't make the situation worse. It
> just doesn't optimize all that it could potentially optimize and
> that's OK.
>
> I've written a separate 0002 patch to address this. Now, before
> generation of paths for bitmap OR, similar OR entries are grouped
> together. When considering a group of similar entries, they are
> considered both together and one-by-one. Ideally we could consider
> more sophisticated grouping, but that seems fine for now. You can
> check how this patch handles the cases of above.
>
> Also, 0002 address issue of duplicated bitmap scan conditions in
> different forms. During generate_bitmap_or_paths() we need to exclude
> considered condition for other clauses. It couldn't be as normal
> filtered out in the latter stage, because could reach the index in
> another form.
>
> > I agree with you that there is an overhead and your patch fixes this
> > problem, but optimizer needs to have a good ordering of expressions for
> > application.
> >
> > I think we can try to move the transformation to another place where
> > there is already a loop pass, and also save two options "OR" expr and
> > "ANY" expr in one place (through BoolExpr) (like find_duplicate_ors
> > function) and teach the optimizer to determine which option is better,
> > for example, like now in match_orclause_to_indexcol() function.
> >
> > What do you thing about it?
>
> find_duplicate_ors() and similar places were already tried before.
> Please, check upthread. This approach receives severe critics. AFAIU,
> the problem is that find_duplicate_ors() during preprocessing, a
> cost-blind stage.
>
> This is why I'd like to continue developing ideas of v27, because it
> fits the existing framework.

The revised patchset is attached. There is no material changes in the
logic, I found no issues here yet. But it comes with refactoring,
cleanup, more comments and better commit messages. I think now this
patchset is understandable and ready for review.

------
Regards,
Alexander Korotkov
Supabase