[ 
https://issues.apache.org/jira/browse/IGNITE-24307?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17921813#comment-17921813
 ] 
Vladimir Dmitrienko commented on IGNITE-24307:
----------------------------------------------

[~jooger] could you tell which is the approximate number of rows required for 
index to be used?

> Query plan does not use 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)

Reply via email to