Hi Vamsi, *Explain plan:* explain select * from vamsi.table_a where col2 = 'abc'; +-------------------------------------------------------------+ | PLAN | +-------------------------------------------------------------+ | CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A | | SERVER FILTER BY COL2 = 'abc' | +-------------------------------------------------------------+
You are selecting all the columns of the table, which are not part of local index (and are also not covered) so instead of scanning index and join back to data table to get all the columns is costly operation. so optimizer chooses data table to scan instead of using index to serve query. below query should use local indexes:- explain select col2, any_covered_colums from vamsi.table_a where col2 = 'abc'; For covered indexes , you can read https://phoenix.apache.org/secondary_indexing.html Regards, Ankit Singhal On Tue, Jun 28, 2016 at 4:25 AM, Vamsi Krishna <vamsi.attl...@gmail.com> wrote: > Team, > > I'm using HDP 2.3.2 (HBase : 1.1.2, Phoenix : 4.4.0). > *Question: *phoenix explain plan not showing any difference after adding > a local index on the table column that is used in query filter. Can someone > please explain why? > > *Create table:* > CREATE TABLE IF NOT EXISTS VAMSI.TABLE_A (COL1 VARCHAR(36) , COL2 > VARCHAR(36) , COL3 VARCHAR(36) CONSTRAINT TABLE_A_PK PRIMARY KEY (COL1)) > COMPRESSION='SNAPPY', SALT_BUCKETS=5; > *Insert data:* > upsert into vamsi.table_a values ('abc123','abc','123'); > upsert into vamsi.table_a values ('def456','def','456'); > > *Explain plan:* > explain select * from vamsi.table_a where col2 = 'abc'; > +-------------------------------------------------------------+ > | PLAN | > +-------------------------------------------------------------+ > | CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A | > | SERVER FILTER BY COL2 = 'abc' | > +-------------------------------------------------------------+ > > *Create local index:* > CREATE LOCAL INDEX IF NOT EXISTS IDX_TABLE_A_COL2 ON VAMSI.TABLE_A (COL2); > > *Explain plan:* > explain select * from vamsi.table_a where col2 = 'abc'; > +-------------------------------------------------------------+ > | PLAN | > +-------------------------------------------------------------+ > | CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A | > | SERVER FILTER BY COL2 = 'abc' | > +-------------------------------------------------------------+ > > Thanks, > Vamsi Attluri > > -- > Vamsi Attluri >
