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 Thu, 29 Aug 2019 07:52:04 GMT
Thanks, I will get a try

> On Aug 21, 2019, at 5:24 AM, Vincent Poon <vincentpoon@apache.org> wrote:
> 
> 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 <ankitsinghal59@gmail.com <mailto:ankitsinghal59@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 <zhuangzixiaoyou@gmail.com <mailto:zhuangzixiaoyou@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 <elserj@apache.org <mailto: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