v3-0001-Skip-not-SOAP-supported-indexes-while-transformin.patch

From b9fc7d52ebb051fe5f67501407c56daf0af765da Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Fri, 29 Nov 2024 01:46:43 +0200
Subject: [PATCH v3] Skip not SOAP-supported indexes while transforming an OR
 clause into SAOP

There is no point in transforming OR-clauses into SAOP's if the target index
doesn't support SAOP scans anyway.  This commit adds corresponding checks
to match_orclause_to_indexcol() and group_similar_or_args().  The first check
fixes the actual bug, while the second just saves some cycles.

Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/8174de69-9e1a-0827-0e81-ef97f56a5939%40gmail.com
Author: Alena Rybakina
Reviewed-by: Ranier Vilela, Alexander Korotkov
---
 src/backend/optimizer/path/indxpath.c      | 11 +++++++++--
 src/test/regress/expected/create_index.out | 18 ++++++++++++++++++
 src/test/regress/sql/create_index.sql      |  6 ++++++
 3 files changed, 33 insertions(+), 2 deletions(-)

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index d827fc9f4d9..640b28a68fc 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1354,8 +1354,11 @@ group_similar_or_args(PlannerInfo *root, RelOptInfo *rel, RestrictInfo *rinfo)
 		{
 			IndexOptInfo *index = (IndexOptInfo *) lfirst(lc2);
 
-			/* Ignore index if it doesn't support bitmap scans */
-			if (!index->amhasgetbitmap)
+			/*
+			 * Ignore index if it doesn't support bitmap scans or SAOP
+			 * clauses.
+			 */
+			if (!index->amhasgetbitmap || !index->amsearcharray)
 				continue;
 
 			for (colnum = 0; colnum < index->nkeycolumns; colnum++)
@@ -3248,6 +3251,10 @@ match_orclause_to_indexcol(PlannerInfo *root,
 	Assert(IsA(orclause, BoolExpr));
 	Assert(orclause->boolop == OR_EXPR);
 
+	/* Ignore index if it doesn't support SAOP clauses */
+	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..1904eb65bb9 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1233,6 +1233,24 @@ SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
     14
 (1 row)
 
+-- OR-clauses shouldn't be transformed into SAOP because hash indexes don't
+-- support SAOP scans.
+SET enable_seqscan = off;
+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 enable_seqscan;
 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..c085e05f052 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -372,6 +372,12 @@ 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';
+-- OR-clauses shouldn't be transformed into SAOP because hash indexes don't
+-- support SAOP scans.
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM tenk1 WHERE stringu1 = 'TVAAAA' OR  stringu1 = 'TVAAAB';
+RESET enable_seqscan;
 DROP INDEX hash_tuplesort_idx;
 RESET maintenance_work_mem;
 
-- 
2.39.5 (Apple Git-154)