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 08:49:05 GMT
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 <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