Thanks Vikash. On Wed, Jun 29, 2016 at 12:26 PM Talanki, Vikash <vtala...@visa.com> wrote:
> Hi Ankit, > > I think this is not completely true. > Phoenix uses local index table when all columns in where clause and > atleast one column in select clause are part of local index table. > > "Select col1,col2,col3 from table" uses local index but "select * from > table" do not. > I'm not certain of the reason > > > Sent with Good Work (www.good.com) > > *From: *Ankit Singhal <ankitsingha...@gmail.com> > *Date: *Wednesday, Jun 29, 2016, 12:15 > *To: *user@phoenix.apache.org <user@phoenix.apache.org> > *Subject: *Re: phoenix explain plan not showing any difference after > adding a local index on the table column that is used in query filter > > 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 >> > > -- Vamsi Attluri
