Phoenix not using local index

william_prender — Fri, 14 Apr 2017 21:59:24 GMT

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

Re: Phoenix not using local index

rmaruthiyodan — Tue, 02 May 2017 09:55:48 GMT

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".

question Re: Phoenix not using local index in Archives of Support Questions (Read Only)

Phoenix not using local index

Re: Phoenix not using local index