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

On 2/3/25 00:57, Alexander Korotkov wrote:
> On Thu, Jan 30, 2025 at 3:23 PM Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)com> wrote:
>> On Tue, 28 Jan 2025 at 12:42, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>>>
>>> On 1/28/25 11:36, Andrei Lepikhov wrote:
>>>> On 1/27/25 16:50, Alexander Korotkov wrote:
>>>> qsort(matches, n, sizeof(OrArgIndexMatch), or_arg_index_match_cmp);
>>>>
>>>> To fit an index, the order of elements in the target array of the
>>>> `ScalarArrayOpExpr` may change compared to the initial list of OR
>>>> expressions. If there are indexes that cover the same set of columns but
>>>> in reverse order, this could potentially alter the position of a
>>>> Subplan. However, I believe this is a rare case; it is supported by the
>>>> initial OR path and should be acceptable.
>>> I beg your pardon - I forgot that we've restricted the feature's scope
>>> and can't combine OR clauses into ScalarArrayOpExpr if the args list
>>> contains references to different columns.
>>> So, my note can't be applied here.
>>>
>>> --
>>> regards, Andrei Lepikhov
>>
>> I've looked at the patch v46-0001
>> Looks good to me.
>>
>> There is a test that demonstrates the behavior change. Maybe some more
>> cases like are also worth adding to a test.
>>
>> +SELECT * FROM bitmap_split_or t1, bitmap_split_or t2 WHERE t1.a=t2.c
>> OR (t1.a=t2.b OR t1.a=1);
>> + QUERY PLAN
>> +--------------------------------------------------------
>> + Nested Loop
>> + -> Seq Scan on bitmap_split_or t2
>> + -> Index Scan using t_a_b_idx on bitmap_split_or t1
>> + Index Cond: (a = ANY (ARRAY[t2.c, t2.b, 1]))
>> +(4 rows)
>> +
>> +EXPLAIN (COSTS OFF)
>> +SELECT * FROM bitmap_split_or t1, bitmap_split_or t2 WHERE t1.c=t2.b OR t1.a=1;
>> + QUERY PLAN
>> +----------------------------------------------
>> + Nested Loop
>> + Join Filter: ((t1.c = t2.b) OR (t1.a = 1))
>> + -> Seq Scan on bitmap_split_or t1
>> + -> Materialize
>> + -> Seq Scan on bitmap_split_or t2
>> +(5 rows)
>> +
>> +EXPLAIN (COSTS OFF)
>
> Added more tests to join.sql
I have made final pass through the changes. All looks good.
Only one thing looks strange for me - multiple '42's in the output of
the test. May be reduce output by an aggregate in the target list of the
query?

--
regards, Andrei Lepikhov