[
https://issues.apache.org/jira/browse/IGNITE-24767?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Iurii Gerzhedovich reassigned IGNITE-24767:
-------------------------------------------
Assignee: (was: Iurii Gerzhedovich)
> [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, 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
>
> 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)
