Hi!
On 03.09.2024 12:52, Andrei Lepikhov wrote:
If OR constants have different types, then they belong to different
groups, and I think that's unfair. I think that conversion to a
single type should be used here - while I’m working on this, I’ll
send the code in the next letter.
IMO, that means additional overhead, isn't it? It is an improvement
and I suggest to discuss it in a separate thread if current feature
will be applied.
I think we will have a slight overhead, so in essence it will go
additionally through the transformed groups, and not through the entire
list of Or expressions. I agree with your suggestion to discuss it in
separate thread.
And I noticed that there were some tests missing on this, so I added
this.
I've updated the patch file to include my and Jian's suggestions, as
well as the diff file if there's no objection.
I doubt if you really need additional index on the tenk1 table. What
is the case you can't reproduce with current indexes, playing, let's
say, with casting to numeric and integer data types?
See in attachment minor fixes to the v38 version of the patch set.
I rewrote the tests with integer types. Thanks for your suggestion. If
you don't mind, I've updated the diff file you attached earlier to
include the tests.
diff --git a/src/backend/optimizer/path/indxpath.c
b/src/backend/optimizer/path/indxpath.c
index cde635d9cb6..d54462f0fce 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3345,10 +3345,8 @@ match_orclause_to_indexcol(PlannerInfo *root,
get_typlenbyvalalign(consttype, &typlen, &typbyval, &typalign);
elems = (Datum *) palloc(sizeof(Datum) * list_length(consts));
- foreach(lc, consts)
+ foreach_node(Const, value, consts)
{
- Const *value = (Const *) lfirst(lc);
-
Assert(!value->constisnull && value->constvalue);
elems[i++] = value->constvalue;
diff --git a/src/test/regress/expected/create_index.out
b/src/test/regress/expected/create_index.out
index 5623f4b3123..103e135de9f 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1890,52 +1890,45 @@ SELECT * FROM tenk1
Index Cond: ((thousand = 42) AND (tenthous = ANY
('{1,3,42}'::integer[])))
(8 rows)
-create index stringu1_idx on tenk1 (stringu1);
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
- WHERE thousand = 42 AND (stringu1::text = 'MAAAAA' OR stringu1::text =
'TUAAAA'::name OR stringu1 = 'OBAAAA'::name);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
+ WHERE thousand = 42 AND (tenthous = 1::int2 OR tenthous::int2 = 3::int8 OR
tenthous = 42::int8);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1
Recheck Cond: (thousand = 42)
- Filter: (((stringu1)::text = 'MAAAAA'::text) OR ((stringu1)::text =
'TUAAAA'::name) OR (stringu1 = 'OBAAAA'::name))
+ Filter: ((tenthous = '1'::smallint) OR ((tenthous)::smallint = '3'::bigint)
OR (tenthous = '42'::bigint))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = 42)
(5 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
- WHERE thousand = 42 AND (stringu1 = 'MAAAAA' OR stringu1 = 'TUAAAA' OR
stringu1 = 'OBAAAA');
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
+ WHERE thousand = 42 AND (tenthous = 1::int2 OR tenthous::int2 = 3::int8 OR
tenthous::int2 = 42::int8);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1
- Recheck Cond: ((thousand = 42) AND ((stringu1 = 'MAAAAA'::name) OR
(stringu1 = 'TUAAAA'::name) OR (stringu1 = 'OBAAAA'::name)))
- -> BitmapAnd
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 42)
- -> Bitmap Index Scan on stringu1_idx
- Index Cond: (stringu1 = ANY ('{MAAAAA,TUAAAA,OBAAAA}'::name[]))
-(7 rows)
+ Recheck Cond: (thousand = 42)
+ Filter: ((tenthous = '1'::smallint) OR ((tenthous)::smallint = '3'::bigint)
OR ((tenthous)::smallint = '42'::bigint))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 42)
+(5 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
- WHERE thousand = 42 AND (stringu1 = 'MAAAAA' OR stringu1 = 'TUAAAA'::text OR
stringu1 = 'OBAAAA'::text);
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
+ WHERE thousand = 42 AND (tenthous = 1::int2 OR tenthous = 3::int8 OR
tenthous = 42::int8);
+ QUERY
PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1
- Recheck Cond: ((thousand = 42) AND ((stringu1 = 'MAAAAA'::name) OR
((stringu1 = 'TUAAAA'::text) OR (stringu1 = 'OBAAAA'::text))))
- Filter: ((stringu1 = 'MAAAAA'::name) OR (stringu1 = 'TUAAAA'::text) OR
(stringu1 = 'OBAAAA'::text))
- -> BitmapAnd
+ Recheck Cond: (((thousand = 42) AND ((tenthous = '3'::bigint) OR (tenthous
= '42'::bigint))) OR ((thousand = 42) AND (tenthous = '1'::smallint)))
+ Filter: ((tenthous = '1'::smallint) OR (tenthous = '3'::bigint) OR
(tenthous = '42'::bigint))
+ -> BitmapOr
-> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 42)
- -> BitmapOr
- -> Bitmap Index Scan on stringu1_idx
- Index Cond: (stringu1 = 'MAAAAA'::name)
- -> Bitmap Index Scan on stringu1_idx
- Index Cond: (stringu1 = ANY ('{TUAAAA,OBAAAA}'::text[]
COLLATE "C"))
-(11 rows)
+ Index Cond: ((thousand = 42) AND (tenthous = ANY
('{3,42}'::bigint[])))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: ((thousand = 42) AND (tenthous = '1'::smallint))
+(8 rows)
-Drop index stringu1_idx;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff --git a/src/test/regress/sql/create_index.sql
b/src/test/regress/sql/create_index.sql
index 37538ab6bba..83e2769801b 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -742,18 +742,18 @@ EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42 or
tenthous is null);
-create index stringu1_idx on tenk1 (stringu1);
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
- WHERE thousand = 42 AND (stringu1::text = 'MAAAAA' OR stringu1::text =
'TUAAAA'::name OR stringu1 = 'OBAAAA'::name);
+ WHERE thousand = 42 AND (tenthous = 1::int2 OR tenthous::int2 = 3::int8 OR
tenthous = 42::int8);
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
- WHERE thousand = 42 AND (stringu1 = 'MAAAAA' OR stringu1 = 'TUAAAA' OR
stringu1 = 'OBAAAA');
+ WHERE thousand = 42 AND (tenthous = 1::int2 OR tenthous::int2 = 3::int8 OR
tenthous::int2 = 42::int8);
+
+
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
- WHERE thousand = 42 AND (stringu1 = 'MAAAAA' OR stringu1 = 'TUAAAA'::text OR
stringu1 = 'OBAAAA'::text);
-Drop index stringu1_idx;
+ WHERE thousand = 42 AND (tenthous = 1::int2 OR tenthous = 3::int8 OR
tenthous = 42::int8);
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
