[
https://issues.apache.org/jira/browse/IGNITE-24307?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Dmitrienko updated IGNITE-24307:
-----------------------------------------
Description:
*Steps to reproduce:*
Execute the following SQL script:
{code:java}
CREATE TABLE IF NOT EXISTS test_table (id int primary key, field_1 int, field_2
int, field_3 int);
CREATE INDEX IF NOT EXISTS one_field_index ON test_table (field_1 ASC);
CREATE INDEX IF NOT EXISTS three_field_index ON test_table (field_1 ASC,
field_2 ASC, field_3 ASC);
INSERT INTO test_table VALUES (1, 2, 3, 4);
EXPLAIN PLAN FOR SELECT field_1, field_2, field_3 FROM test_table WHERE field_1
= 2 AND field_2 = 3 AND field_3 = 4;{code}
*Actual result:*
Execution plan uses the {{ONE_FIELD_INDEX}} even if the more appropriate
{{THREE_FIELD_INDEX}} exists;
{code:java}
PLAN
Exchange(distribution=[single]): rowcount = 1.0, cumulative cost = IgniteCost
[rowCount=2.0, cpu=2.0, memory=1.0, io=1.0, network=13.0], id = 362
IndexScan(table=[[PUBLIC, TEST_TABLE]], tableId=[35], index=[ONE_FIELD_INDEX],
type=[SORTED], searchBounds=[[ExactBounds [bound=2]]], filters=[AND(=($t0, 2),
=($t1, 3), =($t2, 4))], requiredColumns=[{1, 2, 3}], collation=[[1]]): rowcount
= 1.0, cumulative cost = IgniteCost [rowCount=1.0, cpu=1.0, memory=1.0, io=1.0,
network=1.0], id = 361 {code}
*Expected result:*
{{THREE_FIELD_INDEX}} used.
*Note:*
if id column added to the query, {{THREE_FIELD_INDEX}} is used.
was:
*Steps to reproduce:*
Execute the following SQL script:
{code:java}
CREATE TABLE IF NOT EXISTS test_table (id int primary key, field_1 int, field_2
int, field_3 int);
CREATE INDEX IF NOT EXISTS one_field_index ON test_table (field_1 ASC);
CREATE INDEX IF NOT EXISTS three_field_index ON test_table (field_1 ASC,
field_2 ASC, field_3 ASC);
INSERT INTO test_table VALUES (1, 2, 3, 4);
EXPLAIN PLAN FOR SELECT field_1, field_2, field_3 FROM test_table WHERE field_1
= 2 AND field_2 = 3 AND field_3 = 4;{code}
*Actual result:*
Execution plan uses the {{ONE_FIELD_INDEX}} even if the more appropriate
{{THREE_FIELD_INDEX}} exists;
{code:java}
PLAN
Exchange(distribution=[single]): rowcount = 1.0, cumulative cost = IgniteCost
[rowCount=2.0, cpu=2.0, memory=1.0, io=1.0, network=13.0], id = 362
IndexScan(table=[[PUBLIC, TEST_TABLE]], tableId=[35], index=[ONE_FIELD_INDEX],
type=[SORTED], searchBounds=[[ExactBounds [bound=2]]], filters=[AND(=($t0, 2),
=($t1, 3), =($t2, 4))], requiredColumns=[{1, 2, 3}], collation=[[1]]): rowcount
= 1.0, cumulative cost = IgniteCost [rowCount=1.0, cpu=1.0, memory=1.0, io=1.0,
network=1.0], id = 361 {code}
*Expected result:*
{{THREE_FIELD_INDEX}} used.
Note: if id column added to the query, {{THREE_FIELD_INDEX}} is used.
> Query plan uses not the most optimal table index
> ------------------------------------------------
>
> Key: IGNITE-24307
> URL: https://issues.apache.org/jira/browse/IGNITE-24307
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 3.0.0-beta1
> Environment: 3 nodes (each node is CMG, each node
> {color:#067d17}"-Xms4096m"{color}, {color:#067d17}"-Xmx4096m"{color}), each
> on separate host. Each host vCPU: 4, Memory: 32GB.
> Reporter: Vladimir Dmitrienko
> Priority: Major
> Labels: ignite-3
>
> *Steps to reproduce:*
> Execute the following SQL script:
> {code:java}
> CREATE TABLE IF NOT EXISTS test_table (id int primary key, field_1 int,
> field_2 int, field_3 int);
> CREATE INDEX IF NOT EXISTS one_field_index ON test_table (field_1 ASC);
> CREATE INDEX IF NOT EXISTS three_field_index ON test_table (field_1 ASC,
> field_2 ASC, field_3 ASC);
> INSERT INTO test_table VALUES (1, 2, 3, 4);
> EXPLAIN PLAN FOR SELECT field_1, field_2, field_3 FROM test_table WHERE
> field_1 = 2 AND field_2 = 3 AND field_3 = 4;{code}
>
> *Actual result:*
> Execution plan uses the {{ONE_FIELD_INDEX}} even if the more appropriate
> {{THREE_FIELD_INDEX}} exists;
>
> {code:java}
> PLAN
>
>
>
>
> Exchange(distribution=[single]): rowcount = 1.0, cumulative cost = IgniteCost
> [rowCount=2.0, cpu=2.0, memory=1.0, io=1.0, network=13.0], id = 362
>
>
>
> IndexScan(table=[[PUBLIC, TEST_TABLE]], tableId=[35],
> index=[ONE_FIELD_INDEX], type=[SORTED], searchBounds=[[ExactBounds
> [bound=2]]], filters=[AND(=($t0, 2), =($t1, 3), =($t2, 4))],
> requiredColumns=[{1, 2, 3}], collation=[[1]]): rowcount = 1.0, cumulative
> cost = IgniteCost [rowCount=1.0, cpu=1.0, memory=1.0, io=1.0, network=1.0],
> id = 361 {code}
>
> *Expected result:*
> {{THREE_FIELD_INDEX}} used.
> *Note:*
> if id column added to the query, {{THREE_FIELD_INDEX}} is used.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
