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

On Thu, Nov 28, 2024 at 9:33 PM Alena Rybakina
<a(dot)rybakina(at)postgrespro(dot)ru> wrote:
>
> On 28.11.2024 22:28, Ranier Vilela wrote:
>
> Em qui., 28 de nov. de 2024 às 16:03, Alena Rybakina <a(dot)rybakina(at)postgrespro(dot)ru> escreveu:
>>
>> Hi! Thank you for the case.
>>
>> On 28.11.2024 21:00, Alexander Lakhin wrote:
>> > Hello Alexander,
>> >
>> > 21.11.2024 09:34, Alexander Korotkov wrote:
>> >> I'm going to push this if no objections.
>> >
>> > Please look at the following query, which triggers an error after
>> > ae4569161:
>> > SET random_page_cost = 1;
>> > CREATE TABLE tbl(u UUID);
>> > CREATE INDEX idx ON tbl USING HASH (u);
>> > SELECT COUNT(*) FROM tbl WHERE u = '00000000000000000000000000000000' OR
>> > u = '11111111111111111111111111111111';
>> >
>> > ERROR: XX000: ScalarArrayOpExpr index qual found where not allowed
>> > LOCATION: ExecIndexBuildScanKeys, nodeIndexscan.c:1625
>> >
>> >
>> I found out what the problem is index scan method was not generated. We
>> need to check this during OR clauses for SAOP transformation.
>>
>> There is a patch to fix this problem.
>
> Hi.
> Thanks for the quick fix.
>
> But I wonder if it is not possible to avoid all if the index is useless?
> Maybe moving your fix to the beginning of the function?
>
> diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
> index d827fc9f4d..5ea0b27d01 100644
> --- a/src/backend/optimizer/path/indxpath.c
> +++ b/src/backend/optimizer/path/indxpath.c
> @@ -3248,6 +3248,10 @@ match_orclause_to_indexcol(PlannerInfo *root,
> Assert(IsA(orclause, BoolExpr));
> Assert(orclause->boolop == OR_EXPR);
>
> + /* Ignore index if it doesn't support index scans */
> + if(!index->amsearcharray)
> + return NULL;
> +
>
> Agree. I have updated the patch
>
> /*
> * Try to convert a list of OR-clauses to a single SAOP expression. Each
> * OR entry must be in the form: (indexkey operator constant) or (constant
>
> The test bug:
> EXPLAIN SELECT COUNT(*) FROM tbl WHERE u = '00000000000000000000000000000000' OR u = '11111111111111111111111111111111';
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=12.46..12.47 rows=1 width=8)
> -> Bitmap Heap Scan on tbl (cost=2.14..12.41 rows=18 width=0)
> Recheck Cond: ((u = '00000000-0000-0000-0000-000000000000'::uuid) OR (u = '11111111-1111-1111-1111-111111111111'::uuid))
> -> BitmapOr (cost=2.14..2.14 rows=18 width=0)
> -> Bitmap Index Scan on idx (cost=0.00..1.07 rows=9 width=0)
> Index Cond: (u = '00000000-0000-0000-0000-000000000000'::uuid)
> -> Bitmap Index Scan on idx (cost=0.00..1.07 rows=9 width=0)
> Index Cond: (u = '11111111-1111-1111-1111-111111111111'::uuid)
> (8 rows)

I slightly revised the fix and added similar check to
group_similar_or_args(). Could you, please, review that before
commit?

------
Regards,
Alexander Korotkov
Supabase