Support Questions
Find answers, ask questions, and share your expertise

Phoenix not using local index

I am trying this on HDP 2.5.3:

create table if not exists test_table (mykey varchar primary key, col1 varchar, col2
archer);
create local index if not exists idx2 on test_table (col2); 
upsert into test_table (mykey, col1, col2) values('k1', 'v1-1', 'v1-2'); 
upsert into test_table (mykey, col1, col2) values('k2', 'v2-1', 'v2-2'); 
upsert into test_table (mykey, col1, col2) values('a2', 'v2-1', 'v3-2');
upsert into test_table (mykey, col1, col2) values('bv2', 'v2-1', 'v4-2');
upsert into test_table (mykey, col1, col2) values('c2', 'v2-1', 'v5-2');
upsert into test_table (mykey, col1, col2) values('x2', 'v2-1', 'v6-2'); 
upsert into test_table (mykey, col1, col2) values('y2', 'v2-1', 'v7-2');
select using the index
explain select col2 from test_table where col2 = 'v1-2'; 
+----------------------------------------------------------------------------------+
|                                       PLAN                                       |
+----------------------------------------------------------------------------------+ 

| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST_TABLE [1,'v1-2']  |

| SERVER FILTER BY FIRST KEY ONLY |

+----------------------------------------------------------------------------------+ 
2 rows selected (0.008 seconds)
same thing with a hint
explain select /*+ INDEX(test_table idx2) */  col2 from test_table where col2 = 'v1-2';

Any reason it isn't using the index. We have tried adding over 400 rows with the same results.

Thanks

1 ACCEPTED SOLUTION

Cloudera Employee

Although this was answered earlier over a support ticket, updating the details here for any future visitor.

Phoenix in HDP 2.5 and above includes PHOENIX-1734 - where the Phoenix local indexes are co-located in the same region where the corresponding data exists, although on a different column family.

In the above explain plan, the output actually tells that the local index is in use: "RANGE SCAN OVER TEST_TABLE [1,'v1-2']" --> Means its a range scan (instead of a full table scan otherwise), on the data table using the local index (type 1 = local index) and the given value of "v1-2".

View solution in original post

1 REPLY 1

Cloudera Employee

Although this was answered earlier over a support ticket, updating the details here for any future visitor.

Phoenix in HDP 2.5 and above includes PHOENIX-1734 - where the Phoenix local indexes are co-located in the same region where the corresponding data exists, although on a different column family.

In the above explain plan, the output actually tells that the local index is in use: "RANGE SCAN OVER TEST_TABLE [1,'v1-2']" --> Means its a range scan (instead of a full table scan otherwise), on the data table using the local index (type 1 = local index) and the given value of "v1-2".

; ;