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

Ok, thank you for your work)

I think we can leave only the two added libraries in the first patch,
others are superfluous.

On 05.08.2024 22:48, Alexander Korotkov wrote:
> 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

--
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company