On 29.11.2024 03:04, Alexander Korotkov wrote:
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?
I agree with changes. Thank you!
--
Regards,
Alena Rybakina
Postgres Professional
