phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From you Zhuang <zhuangzixiao...@gmail.com>
Subject Re: Is there any way to using appropriate index automatically?
Date Tue, 20 Aug 2019 02:44:28 GMT
You are right, thanks so much, but it’s so limited that I can’t include all columns due
to hundreds of columns existing in one table.

I think covered columns must be queried in global indexes is reasonable, local index isn’t.

Because we query rowkey from local index first , then get actual row from data table. 

Thus we have no necessity to limit local index usage including all queried columns.  



> On Aug 20, 2019, at 12:32 AM, Josh Elser <elserj@apache.org> wrote:
> 
> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used <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 <asaraseka@eztexting.com <mailto:asaraseka@eztexting.com>
<mailto:asaraseka@eztexting.com <mailto:asaraseka@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 <zhuangzixiaoyou@gmail.com <mailto:zhuangzixiaoyou@gmail.com>
<mailto:zhuangzixiaoyou@gmail.com <mailto:zhuangzixiaoyou@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 <tel:380997600401>> *•* asaraseka@eztexting.com
<mailto:asaraseka@eztexting.com> <mailto:asaraseka@eztexting.com <mailto:asaraseka@eztexting.com>>
*•* eztexting.com <http://eztexting.com/><http://eztexting.com/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature
<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://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://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
<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.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.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.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
<https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>>


Mime
View raw message