public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Alexander Korotkov <[email protected]>
To: Alena Rybakina <[email protected]>
Cc: Ranier Vilela <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: jian he <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Nikolay Shaplov <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Peter Geoghegan <[email protected]>
Cc: Marcos Pegoraro <[email protected]>
Cc: [email protected]
Cc: Peter Eisentraut <[email protected]>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: Mon, 13 Jan 2025 10:39:23 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com>
References: <[email protected]>
<CAPpHfdvxF1OZUoJr2bg8cmAnty-KyRkswPa3hCPSmR5gSx7-Yg@mail.gmail.com>
<CACJufxGS_MKqkfnw3BMhfi+=xuf2SAFvwf0Eq3e12XqAQaKdZg@mail.gmail.com>
<[email protected]>
<CAPpHfduzBgV3AecMU0jFqOSjK9iP86HiHEzj2Hv6hLqWu7JJFQ@mail.gmail.com>
<CAPpHfduqOuvh6i=jYDPWnAUg325hsOLUAW9r_awdirQRA7uzHA@mail.gmail.com>
<[email protected]>
<CAPpHfdtPkA3XGM+FOanXfFNm45YEMkuqpfL0ZSL7uOP1bHPPEg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com>
<[email protected]>
<CAPpHfdvM4E-wPCRbYkr9eGH2sSxgOYKZ1iOUyjKYqTEyurFRLQ@mail.gmail.com>
<[email protected]>
<CAPpHfdtgcye9ELaLHw4ZOUxWQ9t1P=oBDJX8KwwOHqasESdXQQ@mail.gmail.com>
<CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com>
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
Attachments:
[text/x-patch] joinorclauses-fix.diff (2.2K, ../[email protected]/2-joinorclauses-fix.diff)
download | inline diff:
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;
view thread (38+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: POC, WIP: OR-clause support for indexes
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox