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

[Vincent Poon]

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 
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 
> 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  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 = 30;
>> 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 > > 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 > mailto:zhuangzixiao...@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
>> > *•* asaras...@eztexting.com <
>> mailto:asaras...@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
>> >
>>
>>
>>

Re: Buckets VS regions

[Ankit Singhal]

What is the Phoenix version(if you are not on the latest version probably
stats inconsistency is already fixed by now), if you are working on test
setup and can reproduce it again, create JIRA and please upload the debug
log of the query, hbase:meta and system.stats on it (remember these may
have sensitive information about your environment and data like hbase:meta
has ip-address/hostname and system.stats has data row keys, so upload only
if you think it's a test data and hostnames have no significance).

Thanks,
Ankit Singhal

On Mon, Aug 19, 2019 at 11:17 PM venkata subbarayudu 
wrote:

> There are few reasons - around corner cases , starting from a region-split
> to any minor compactions during write process, general recommendation is to
> not use guide posts for query processing - or - disable guideposts so the
> consistency is guaranteed
>
>
> On Tue, Aug 20, 2019 at 11:10 AM jesse  wrote:
>
>> Yes, that seems to be a trick, the issue is only associated a region.
>>
>> What could be the causes of guidposts not updated?
>>
>>
>> On Mon, Aug 19, 2019, 6:40 PM venkata subbarayudu 
>> wrote:
>>
>>> Did you try with updating table statistics, it may be because some times
>>> table guideposts are out of sync
>>>
>>> Below is the SQL to update table stats
>>>  Update statistics table
>>> By default above executes asynchronously, hence it may take some time to
>>> update depending on table size
>>>
>>> On Tue 20 Aug, 2019, 6:34 AM jesse,  wrote:
>>>
 And the table is simple and has no index set up.

 On Mon, Aug 19, 2019, 6:03 PM jesse  wrote:

> we got some trouble, maybe someone could shed some light on this.
>
> Table has primary key c1, c2 and c3.
> Table is set with SALT_BUCKETS=12. Now it has 14 regions.
>
> The table has a record with c1='a', c2='b', c3='c'
>
> If Phoenix query is like:
> select * from t where c2='b', it returns some results.
>
> select * from t where c1='a', it returns empty
> select * from t where c2='b' and c1='a', it returns empty
>
> select * from t where c3='c', it returns right results
> select * from t where c2='b' and c3='c', it returns results
>
> What the heck is going wrong? The system used to work fine.
>
>
>
>
>
>
>
>
> On Mon, Aug 19, 2019, 5:33 PM James Taylor 
> wrote:
>
>> It’ll start with 12 regions, but those regions may split as they’re
>> written to.
>>
>> On Mon, Aug 19, 2019 at 4:34 PM jesse  wrote:
>>
>>> I have a table is  SALT_BUCKETS = 12, but it has 14 regions, is
>>> this right?
>>>
>>> Thanks
>>>
>>>
>>>
>
> --
> *Venkata Subbarayudu Amanchi.*
>

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 
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  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 = 30;
> 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  mailto:asaras...@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  mailto:zhuangzixiao...@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
> > *•* asaras...@eztexting.com <
> mailto:asaras...@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
> >
>
>
>

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

[you Zhuang]

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  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 = 30;
>> 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 >>  >> >> 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 >>  >> >> 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
>>> > *•* asaras...@eztexting.com 
>>>  >> > *•* eztexting.com 
>>> >>  
>>> >
>>>  
>>> 
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >