CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC; (Has been filled data with bulkload and index is active)
Query: select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5; select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5; The first query will use index local_c_h_index and result shortly, the second query won’t , and response slowly. Yeah, the local index should be used in both the cases, looks like a bug to me, can you please raise a JIRA in Phoenix project for the same. QueryOptimizer.java may have a relevant code to fix the issue, so the patch would really be appreciated. And, also can you try running "select a,b,c,d,e,f,g,h,i,j,k,m,n from TEST_PHOENIX.APP where c=2 and h = 1 limit 5", and see if index is getting used. Regards, Ankit Singhal On Tue, Aug 20, 2019 at 1:49 AM you Zhuang <zhuangzixiao...@gmail.com> wrote: > Er, I also read the sentence “Unlike global indexes, local indexes *will* use > an index even when all columns referenced in the query are not contained in > the index. This is done by default for local indexes because we know that > the table and index data co-reside on the same region server thus ensuring > the lookup is local.” > > I ‘m totally confused. > > > On Aug 20, 2019, at 12:32 AM, Josh Elser <els...@apache.org> wrote: > > http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used > > On 8/19/19 6:06 AM, you Zhuang wrote: > > Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT > hbase-version: 1.4.6 > Table: > CREATE TABLE test_phoenix.app ( > dt integer not null, > a bigint not null , > b bigint not null , > c bigint not null , > d bigint not null , > e bigint not null , > f bigint not null , > g bigint not null , > h bigint not null , > i bigint not null , > j bigint not null , > k bigint not null , > m decimal(30,6) , > n decimal(30,6) > CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k) > ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 300000; > Index: > CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC; > (Has been filled data with bulkload and index is active) > Query: > select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from > TEST_PHOENIX.APP where c=2 and h = 1 limit 5; > select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5; > The first query will use index local_c_h_index and result shortly, the > second query won’t , and response slowly. > The explain plan is weird, all showing without using index. > > On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka <asaras...@eztexting.com < > mailto:asaras...@eztexting.com <asaras...@eztexting.com>>> wrote: > > We have no problems with that. I mean indexes are used even without hints, > if they're suitable for a query. > Maybe you can share your Phoenix version, query, index definition and exec > plan ? > > On Mon, Aug 19, 2019 at 12:46 PM you Zhuang <zhuangzixiao...@gmail.com < > mailto:zhuangzixiao...@gmail.com <zhuangzixiao...@gmail.com>>> wrote: > > Yeah, I mean no hint , use appropriate index automatically. I > create a local index and a query with corresponding index column > filter in where clause. But the query doesn’t use index, with > index hint it uses it. > > > > -- > Aleksandr Saraseka > DBA > 380997600401 > <tel:380997600401 <380997600401>> *•* asaras...@eztexting.com < > mailto:asaras...@eztexting.com <asaras...@eztexting.com>> *•* > eztexting.com< > http://eztexting.com/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature > > > > < > http://facebook.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature> > < > http://linkedin.com/company/eztexting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature> > < > http://twitter.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature> > < > https://www.youtube.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature> > < > https://www.instagram.com/ez_texting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature> > < > https://www.facebook.com/alex.saraseka?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature> > < > https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature > > > > >
