Re: POC, WIP: OR-clause support for indexes
| От | Ranier Vilela |
|---|---|
| Тема | Re: POC, WIP: OR-clause support for indexes |
| Дата | |
| Msg-id | CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | POC, WIP: OR-clause support for indexes (Teodor Sigaev <teodor@sigaev.ru>) |
| Список | pgsql-hackers |
Em qui., 28 de nov. de 2024 às 16:03, Alena Rybakina <a.rybakina@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;
+
/*
* 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
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;
+
/*
* 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)
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)
best regards,
Ranier Vilela
В списке pgsql-hackers по дате отправления: