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)
Thank you
--
Regards,
Alena Rybakina
Postgres Professional
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index d827fc9f4d9..5ea0b27d014 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
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 1b0a5f0e9e1..ef8bbf4748c 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1233,6 +1233,23 @@ SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
14
(1 row)
+-- Transform OR-clauses to SAOP's shouldn't be chosen
+SET random_page_cost = 1;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM tenk1 WHERE stringu1 = 'TVAAAA' OR stringu1 = 'TVAAAB';
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((stringu1 = 'TVAAAA'::name) OR (stringu1 = 'TVAAAB'::name))
+ -> BitmapOr
+ -> Bitmap Index Scan on hash_tuplesort_idx
+ Index Cond: (stringu1 = 'TVAAAA'::name)
+ -> Bitmap Index Scan on hash_tuplesort_idx
+ Index Cond: (stringu1 = 'TVAAAB'::name)
+(8 rows)
+
+RESET random_page_cost;
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index ddd0d9ad396..0e6529f3f3d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -372,6 +372,11 @@ CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fi
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
+-- Transform OR-clauses to SAOP's shouldn't be chosen
+SET random_page_cost = 1;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM tenk1 WHERE stringu1 = 'TVAAAA' OR stringu1 = 'TVAAAB';
+RESET random_page_cost;
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
