[PHOENIX-1436] the choice of index by phoneix is not correct where do querying

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.1.0
• Fix Version/s: 4.3.0, 3.3.0, 3.2.2, 4.2.2

Environment

phoenix4.1
HBase 0.98.6
hadoop 1.2.1

• Assignee: rajeshbabu
• Reporter: xufeng
• Created: 11/Nov/14 02:29
• Updated: 21/Nov/15 02:17
• Resolved: 22/Nov/14 05:23

Description

1.create INDEX_TEST_TABLE:
create table index_test_table (a varchar not null,b varchar not null,c varchar not null,d varchar,e varchar, f varchar constraint pk primary key(a,b,c));

2.upsert some data into table.

3.create index on 'D' and 'F'
create index INDEX_TEST_TABLE_INDEX_D on INDEX_TEST_TABLE(A,D) include(B,C,E,F);
5 rows affected (0.747 seconds)
0: jdbc:phoenix:10.20.14.35:2181> create index INDEX_TEST_TABLE_INDEX_F on INDEX_TEST_TABLE(A,F) include(B,C,D,E);
5 rows affected (0.748 seconds)

4.query data on 'D'
explain select * from INDEX_TEST_TABLE where A in ('1','2','3','4','5') and D in ('1111','2222','3333');

5. it is ok:
CLIENT PARALLEL 1-WAY SKIP SCAN ON 15 KEYS OVER INDEX_TEST_TABLE_INDEX_D ['1','1111'] - ['5','3333']

6. but query data on 'F'
explain select * from INDEX_TEST_TABLE where A in ('1','2','3','4','5') and F in ('1111','2222','3333');

7. also use the index on 'D' and set filter on 'F'
CLIENT PARALLEL 1-WAY SKIP SCAN ON 5 KEYS OVER INDEX_TEST_TABLE_INDEX_D ['1'] - ['5'] |
SERVER FILTER BY F IN ('1111','2222','3333') |

is this a bug in 4.1 version??