Message-ID: <fc1017ca-877b-4f86-b491-154cf123eedd@gmail.com>
Date: Mon, 3 Feb 2025 13:24:04 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC, WIP: OR-clause support for indexes
To: Alexander Korotkov <aekorotkov@gmail.com>,
 Pavel Borisov <pashkin.elfe@gmail.com>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>,
 Ranier Vilela <ranier.vf@gmail.com>, Alexander Lakhin <exclusion@gmail.com>,
 jian he <jian.universality@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
 Nikolay Shaplov <dhyan@nataraj.su>, pgsql-hackers@lists.postgresql.org,
 pgsql-hackers@postgresql.org, Peter Geoghegan <pg@bowt.ie>,
 Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru,
 Peter Eisentraut <peter@eisentraut.org>
References: <567ED6CA.2040504@sigaev.ru>
 <CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com>
 <3b717020-14d9-425f-8c60-ce336b5a23b3@postgrespro.ru>
 <CAPpHfdvM4E-wPCRbYkr9eGH2sSxgOYKZ1iOUyjKYqTEyurFRLQ@mail.gmail.com>
 <43db6a40-5c5f-4909-ad62-3060b297e686@postgrespro.ru>
 <CAPpHfdtgcye9ELaLHw4ZOUxWQ9t1P=oBDJX8KwwOHqasESdXQQ@mail.gmail.com>
 <CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com>
 <ab8aedce-7a2f-4bb2-bfad-3f1ad91e7336@gmail.com>
 <41ba3d47-2a48-476c-88d4-6ebd889a7af2@gmail.com>
 <CAPpHfdvehQbCpQG9zYr739y2nXQkE=FYsat5VE-pDoB4RDPfBw@mail.gmail.com>
 <88844a88-57dd-4722-b697-15c10f1b81a7@gmail.com>
 <CAPpHfduOn0htAXp6h3gcv1hMUuoQSYfM-sk6TXKcM57C8B4Umw@mail.gmail.com>
 <fdba7c11-4f3e-43d3-ac89-cab82aef0b6f@gmail.com>
 <8cf87fc0-1f79-4427-8134-9245a1a52cf4@gmail.com>
 <CALT9ZEHS76SfGQBuxXYVNRWX4GhfZtZoD9KsxVn-PsMkYB9W+A@mail.gmail.com>
 <CAPpHfdsBZmNt9qUoJBqsQFiVDX1=yCKpuVAt1YnR7JCpP=k8+A@mail.gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <CAPpHfdsBZmNt9qUoJBqsQFiVDX1=yCKpuVAt1YnR7JCpP=k8+A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/fc1017ca-877b-4f86-b491-154cf123eedd%40gmail.com>
Precedence: bulk

On 2/3/25 00:57, Alexander Korotkov wrote:
> On Thu, Jan 30, 2025 at 3:23 PM Pavel Borisov <pashkin.elfe@gmail.com> wrote:
>> On Tue, 28 Jan 2025 at 12:42, Andrei Lepikhov <lepihov@gmail.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