[IGNITE-19615] Index is not used while performing SELECT over an indexed column

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.0
• Component/s: sql
• Labels: ignite-3
• Assignee: Maksim Zhuravkov
• Reporter: Ivan Artiukhov
• Created: 01/Jun/23 08:44
• Updated: 04/Sep/24 16:09
• Resolved: 04/Jul/23 15:19

Description

Apache Ignite 3, rev. faac3854f687daab2de4580fd9666fb227bf4c3a
 
Performing SELECT on an indexed column does not utilize the index. This is shown on EXPLAIN query.
 

11:35:27.760 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Update: create table index_test_table_1(id INTEGER not null, field_1 TINYINT, field_2 SMALLINT, field_3 INTEGER, field_4 FLOAT, field_5 VARCHAR(50), primary key (id))
11:35:28.924 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Update: CREATE INDEX index_test_index_1_1 ON index_test_table_1 (field_2)
Jun 01, 2023 11:35:28 AM org.apache.ignite.internal.logger.IgniteLogger logInternal
INFO: Partition assignment change notification received [remoteAddress=localhost:10801]
11:35:29.003 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Update: INSERT INTO index_test_table_1 (id, field_1, field_2, field_3, field_4, field_5) VALUES (0, 0, 0, 0, 0.0, 'string_value_0')
11:35:30.529 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Update: INSERT INTO index_test_table_1 (id, field_1, field_2, field_3, field_4, field_5) VALUES (1, 1, 1, 1, 1.1, 'string_value_1')
11:35:30.571 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Update: INSERT INTO index_test_table_1 (id, field_1, field_2, field_3, field_4, field_5) VALUES (2, 2, 2, 2, 2.2, 'string_value_2')
...
11:35:33.189 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Update: INSERT INTO index_test_table_1 (id, field_1, field_2, field_3, field_4, field_5) VALUES (97, 97, 97, 97, 106.7, 'string_value_97')
11:35:33.212 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Update: INSERT INTO index_test_table_1 (id, field_1, field_2, field_3, field_4, field_5) VALUES (98, 98, 98, 98, 107.80000000000001, 'string_value_98')
11:35:33.236 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Update: INSERT INTO index_test_table_1 (id, field_1, field_2, field_3, field_4, field_5) VALUES (99, 99, 99, 99, 108.9, 'string_value_99')
11:35:33.258 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Query: SELECT * FROM index_test_table_1 WHERE field_2 = 50
11:35:33.513 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Query: explain plan for SELECT * FROM index_test_table_1 WHERE field_2 = 50
11:35:33.565 [Test worker] INFO org.gridgain.ai3tests.tests.IndexTests – Result: IgniteExchange(distribution=[single]): rowcount = 1500.0, cumulative cost = IgniteCost [rowCount=11500.0, cpu=41500.0, memory=0.0, io=240000.0, network=36000.0], id = 10103
IgniteTableScan(table=[[PUBLIC, INDEX_TEST_TABLE_1]], tableId=[8239005d-346b-40d9-afe7-c15923ef3020], filters=[=(CAST($t2):INTEGER, 50)], requiredColumns=[

{0, 1, 2, 3, 4, 5}

]): rowcount = 1500.0, cumulative cost = IgniteCost [rowCount=10000.0, cpu=40000.0, memory=0.0, io=240000.0, network=0.0], id = 10102

It is expected that explain plan for SELECT * FROM index_test_table_1 WHERE field_2 = 50 query will contain IgniteIndexScan` with `index_test_index_1_1.

Issue Links

• causes: IGNITE-19976 Sql: ClassCastException when trying to select from indexed smallint column
• is caused by: IGNITE-19128 Sql. Custom data types. IgniteIndexScan has incorrect types in searchBounds.
• is related to: IGNITE-19997 Sql. Enhancing test coverage of type coercion
• links to: GitHub Pull Request #2196
• links to: https://github.com/apache/ignite-3/pull/2196