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.
--
Regards,
Alena Rybakina
Postgres Professional
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index d827fc9f4d9..61110db65dd 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3303,6 +3303,10 @@ match_orclause_to_indexcol(PlannerInfo *root,
if (get_op_rettype(opno) != BOOLOID)
break;
+ /* Ignore index if it doesn't support index scans */
+ if(!index->amsearcharray)
+ break;
+
/*
* Check for clauses of the form: (indexkey operator constant) or
* (constant operator indexkey). Determine indexkey side first, check
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;
