phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ankit Singhal <ankitsingha...@gmail.com>
Subject Re: Is there any way to using appropriate index automatically?
Date Tue, 20 Aug 2019 18:38:18 GMT
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>
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> 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 <asaraseka@eztexting.com <
> mailto:asaraseka@eztexting.com <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 <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 <380997600401>> *•* asaraseka@eztexting.com <
> mailto:asaraseka@eztexting.com <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