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 Mon, 19 Aug 2019 10:06:01 GMT
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> 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>>
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> •  asaraseka@eztexting.com <mailto:asaraseka@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>


Mime
View raw message