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)
>
Thank you
--
Regards,
Alena Rybakina
Postgres Professional