Vladimir Dmitrienko created IGNITE-24767:
--------------------------------------------
Summary: [SQL] IndexScan is never used when a cached execution
plan relies on TableScan
Key: IGNITE-24767
URL: https://issues.apache.org/jira/browse/IGNITE-24767
Project: Ignite
Issue Type: Bug
Components: sql
Affects Versions: 3.0.0-beta1, 3.0
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
After the implementation of IGNITE-24307, the IndexScan is only used after the
statistics have been updated. This update occurs once per minute, meaning that
up to one minute may pass before an index becomes usable after being added.
However, if the query execution plan relying on TableScan has been cached, the
IndexScan will never be used unless the cluster is reinitialized or the
{{FORCE_INDEX}} hint is applied.
This leads to a situation where a user will not see any performance gains for a
particular query after adding an index. See the following cases:
*Case 1. Query executed before an Index was created uses TableScan forever:*
{code:java}
// 1. Create a table.
CREATE TABLE IF NOT EXISTS table_1 (id int primary key, field_1 int);
// 2. Add at least 5 rows (as we need more than 4 rows for index to be used).
INSERT INTO table_1 VALUES (1, 1);
INSERT INTO table_1 VALUES (2, 2);
INSERT INTO table_1 VALUES (3, 3);
INSERT INTO table_1 VALUES (4, 4);
INSERT INTO table_1 VALUES (5, 5);
// 3. Query by one of these rows:
EXPLAIN PLAN FOR SELECT field_1 FROM table_1 WHERE field_1 = 3;
// 4. Add index:
CREATE INDEX IF NOT EXISTS field_1_index ON table_1 (field_1 ASC);
// 5. Wait for 1 minute until statistics updated.
// 6. Run the same query:
EXPLAIN PLAN FOR SELECT field_1 FROM table_1 WHERE field_1 = 3;{code}
*Actual result:* TableScan used.
*Expected result:* IndexScan used.
*Note:* querying by an another row yields IndexScan as the query hasn't been
cached before.
*Case 2. The first query executed after an Index was created* {*}uses TableScan
forever:{*}{*}{*}
{code:java}
// 1. Create a table.
CREATE TABLE IF NOT EXISTS table_1 (id int primary key, field_1 int);
// 2. Add index:
CREATE INDEX IF NOT EXISTS field_1_index ON table_1 (field_1 ASC);
// 3. Add at least 5 rows (as we need more than 4 rows for index to be used).
INSERT INTO table_1 VALUES (1, 1);
INSERT INTO table_1 VALUES (2, 2);
INSERT INTO table_1 VALUES (3, 3);
INSERT INTO table_1 VALUES (4, 4);
INSERT INTO table_1 VALUES (5, 5);
// 4. Wait for 1 minute until statistics updated.
// 5. Query by one of table rows:
EXPLAIN PLAN FOR SELECT field_1 FROM table_1 WHERE field_1 = 3;{code}
*Actual result:* TableScan used.
*Expected result:* IndexScan used.
*Note:* querying by an another row yields Index scan.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
