check out PHOENIX-5109 , it likely fixes your issue. Unfortunately it's targeted for 4.15.0 which hasn't been released yet. Maybe you can backport and see if it works for your query.
On Tue, Aug 20, 2019 at 11:38 AM Ankit Singhal <ankitsingha...@gmail.com> wrote: > 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 >> > >> >> >>
