Is there any way to using appropriate index automatically?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Is there any way to using appropriate index automatically?

you Zhuang
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.
Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

Aleksandr Saraseka-2
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 <[hidden email]> 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
<a href="tel:380997600401" style="color:rgb(80,80,80);text-decoration:none;font-family:Arial" target="_blank">380997600401
   [hidden email]    eztexting.com
Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

you Zhuang
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 <[hidden email]> 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 <[hidden email]> 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
<a href="tel:380997600401" style="color:rgb(80,80,80);text-decoration:none;font-family:Arial" target="_blank" class="">380997600401
   [hidden email]    eztexting.com

Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

Josh Elser-2
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
>> <[hidden email] <mailto:[hidden email]>> 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 <[hidden email]
>> <mailto:[hidden email]>> 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> *•* [hidden email]
>> <mailto:[hidden email]> *•* 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>
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

you Zhuang
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 <[hidden email]> 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 <[hidden email] <[hidden email]>> 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 <[hidden email] <[hidden email]>> 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
<<a href="tel:380997600401" class="">tel:380997600401> *•* [hidden email] <[hidden email]> *•* 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>

Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

you Zhuang
In reply to this post by Josh Elser-2
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 <[hidden email]> 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 <[hidden email] <[hidden email]>> 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 <[hidden email] <[hidden email]>> 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
<<a href="tel:380997600401" class="">tel:380997600401> *•* [hidden email] <[hidden email]> *•* 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>

Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

Ankit Singhal
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <[hidden email]>> 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 <[hidden email] <[hidden email]>> 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
<<a href="tel:380997600401" target="_blank">tel:380997600401> *•* [hidden email] <[hidden email]> *•* 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>

Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

Vincent Poon-2
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <[hidden email]>> 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 <[hidden email] <[hidden email]>> 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
<<a href="tel:380997600401" target="_blank">tel:380997600401> *•* [hidden email] <[hidden email]> *•* 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>

Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

Josh Elser-2
In reply to this post by you Zhuang
Sorry 'bout that. Missed that you were doing a local index. Thanks for
catching my slack, Ankit and Vincent.

On 8/20/19 4:49 AM, you Zhuang 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 <[hidden email]
>> <mailto:[hidden email]>> 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
>>>> <[hidden email]
>>>> <mailto:[hidden email]><mailto:[hidden email]>> 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
>>>> <[hidden email]
>>>> <mailto:[hidden email]><mailto:[hidden email]>> 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> *•*[hidden email]
>>>> <mailto:[hidden email]><mailto:[hidden email]>
>>>> *•*eztexting.com
>>>> <http://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>
>
Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

you Zhuang
In reply to this post by Ankit Singhal
No, the index can’t be used .

On Aug 21, 2019, at 2:38 AM, Ankit Singhal <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <[hidden email]>> 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 <[hidden email] <[hidden email]>> 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
<<a href="tel:380997600401" target="_blank" class="">tel:380997600401> *•* [hidden email] <[hidden email]> *•* 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>


Reply | Threaded
Open this post in threaded view
|

Re: Is there any way to using appropriate index automatically?

you Zhuang
In reply to this post by Vincent Poon-2
Thanks, I will get a try

On Aug 21, 2019, at 5:24 AM, Vincent Poon <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <[hidden email]>> 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 <[hidden email] <[hidden email]>> 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
<<a href="tel:380997600401" target="_blank" class="">tel:380997600401> *•* [hidden email] <[hidden email]> *•* 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>