Vladimir Dmitrienko created IGNITE-24307:
--------------------------------------------
Summary: 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
*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)
