On 1/13/25 01:39, Alexander Korotkov wrote:
The revised patch is attached. Most notably it revises
group_similar_or_args() to have the same notion of const-ness as
others. In that function we split potential index key and constant
early to save time on enumerating all possible index keys. But it
appears to be possible to split by relids bitmapsets: index key should
use our relid, while const shouldn't. Other that that, comments,
commit message and naming are revised.
Hmm, I would say we should carefully review this code.
Curiously, this patch has activated the dormant problem of duplicated
clauses in joinorclauses. Look:
EXPLAIN (COSTS OFF)
SELECT * FROM bitmap_split_or t1, bitmap_split_or t2
WHERE t1.a=t2.b OR t1.a=1;
Nested Loop
-> Seq Scan on bitmap_split_or t2
-> Bitmap Heap Scan on bitmap_split_or t1
Recheck Cond: (((a = t2.b) OR (a = 1)) AND
((a = t2.b) OR (a = 1)))
-> Bitmap Index Scan on t_a_b_idx
Index Cond: ((a = ANY (ARRAY[t2.b, 1])) AND
(a = ANY (ARRAY[t2.b, 1])))
It can be resolved with a single-line change (see attached). But I need
some time to ponder over the changing behaviour when a clause may match
an index and be in joinorclauses.
--
regards, Andrei Lepikhov
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c03547a997..2a86298d9d 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -2440,7 +2440,7 @@ match_join_clauses_to_index(PlannerInfo *root,
/* Potentially usable, so see if it matches the index or is an OR */
if (restriction_is_or_clause(rinfo))
- *joinorclauses = lappend(*joinorclauses, rinfo);
+ *joinorclauses = list_append_unique_ptr(*joinorclauses, rinfo);
match_clause_to_index(root, rinfo, index, clauseset);
}
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 8011c141bf..47bdee1d41 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -3255,6 +3255,16 @@ CREATE INDEX t_b_c_idx ON bitmap_split_or (b, c);
CREATE STATISTICS t_a_b_stat (mcv) ON a, b FROM bitmap_split_or;
CREATE STATISTICS t_b_c_stat (mcv) ON b, c FROM bitmap_split_or;
ANALYZE bitmap_split_or;
+EXPLAIN (COSTS OFF)
+SELECT * FROM bitmap_split_or t1, bitmap_split_or t2 WHERE t1.a=t2.b OR t1.a=1;
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop
+ -> Seq Scan on bitmap_split_or t2
+ -> Index Scan using t_a_b_idx on bitmap_split_or t1
+ Index Cond: (a = ANY (ARRAY[t2.b, 1]))
+(4 rows)
+
EXPLAIN (COSTS OFF)
SELECT * FROM bitmap_split_or WHERE a = 1 AND (b = 1 OR b = 2) AND c = 2;
QUERY PLAN
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 068c66b95a..bc12d2f098 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1367,6 +1367,8 @@ CREATE STATISTICS t_a_b_stat (mcv) ON a, b FROM bitmap_split_or;
CREATE STATISTICS t_b_c_stat (mcv) ON b, c FROM bitmap_split_or;
ANALYZE bitmap_split_or;
EXPLAIN (COSTS OFF)
+SELECT * FROM bitmap_split_or t1, bitmap_split_or t2 WHERE t1.a=t2.b OR t1.a=1;
+EXPLAIN (COSTS OFF)
SELECT * FROM bitmap_split_or WHERE a = 1 AND (b = 1 OR b = 2) AND c = 2;
DROP TABLE bitmap_split_or;
