On 26/8/2024 12:41, Alena Rybakina wrote:
On 26.08.2024 06:12, jian he wrote:
On Fri, Aug 23, 2024 at 8:58 PM Alexander Korotkov<aekorot...@gmail.com> wrote:
+ int indexnum; /* index of the matching index */
+ int colnum; /* index of the matching column */
I am not 100% sure about the comments.
indexnum: index of the matching index reside in rel->indexlist that
matches (counting from 0)
colnum: the column number of the matched index (counting from 0)
Hmm, it is not easy to invent an alternative variant. What are you
proposing exactly?
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.
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.
--
regards, Andrei Lepikhov
diff --git a/src/backend/optimizer/path/indxpath.c
b/src/backend/optimizer/path/indxpath.c
index cde635d9cb..d54462f0fc 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 5623f4b312..afae2cc272 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1877,7 +1877,7 @@ SELECT * FROM tenk1
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
- WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42 or
tenthous is null);
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42 OR
tenthous IS NULL);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1
@@ -1890,7 +1890,7 @@ SELECT * FROM tenk1
Index Cond: ((thousand = 42) AND (tenthous = ANY
('{1,3,42}'::integer[])))
(8 rows)
-create index stringu1_idx on tenk1 (stringu1);
+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);
@@ -1935,7 +1935,7 @@ SELECT * FROM tenk1
Index Cond: (stringu1 = ANY ('{TUAAAA,OBAAAA}'::text[]
COLLATE "C"))
(11 rows)
-Drop index stringu1_idx;
+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 37538ab6bb..50f76823d9 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -740,9 +740,9 @@ SELECT * FROM tenk1
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
- WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42 or
tenthous is null);
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42 OR
tenthous IS NULL);
-create index stringu1_idx on tenk1 (stringu1);
+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);
@@ -753,7 +753,7 @@ SELECT * FROM tenk1
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (stringu1 = 'MAAAAA' OR stringu1 = 'TUAAAA'::text OR
stringu1 = 'OBAAAA'::text);
-Drop index stringu1_idx;
+DROP INDEX stringu1_idx;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
