On Thu, Nov 28, 2024 at 9:33 PM Alena Rybakina <a.rybak...@postgrespro.ru> wrote: > > On 28.11.2024 22:28, Ranier Vilela wrote: > > Em qui., 28 de nov. de 2024 às 16:03, Alena Rybakina > <a.rybak...@postgrespro.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
v3-0001-Skip-not-SOAP-supported-indexes-while-transformin.patch
Description: Binary data
