You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Jacobo Coll <ja...@gmail.com> on 2016/02/03 13:01:42 UTC

Local index not used with non covered queries

Hi,

I recently started testing HBase/Phoenix as a storage solution for our
data. The problem is that I am not able to execute some "simple queries". I
am using phoenix 4.5.2 and hbase 1.0.0-cdh5.4.0. After creating a table and
making some selects (full scan), I started using local indexes to
accelerate it. As I know, global indexes only work for covered queries, but:

Unlike global indexes, local indexes *will* use an index even when all
> columns referenced in the query are not contained in the index.
>

Knowing this, I prepared a simple test. A table with 3 columns: the primary
key, a locally indexed column and a non indexed column.
Querying by the indexed column works as expected, but if I try to use both
columns, it does a full scan.

I know that I can include both columns on the index, but this is not
supposed to be required.

In a previous mail I wrote a more detailed example of the test:
http://mail-archives.apache.org/mod_mbox/phoenix-user/201512.mbox/%3CCAOJgazo-oCahZxsXrdOOsJxm-DVp7SzcNZhDDNJ2DWzkiNhfvA%40mail.gmail.com%3E

Am I doing something wrong, or missing some step?
Or it is impossible what I am trying to do?
Is there some way to do this without include the other columns in the index?

Thanks,
Jacobo Coll

Re: Local index not used with non covered queries

Posted by "rajeshbabu@apache.org" <ch...@gmail.com>.
bq. if the where condition refers to an indexed column and a non indexed
column, it should use the index?

This case also we will not use the index because we need to know the values
of non indexed columns first to apply the filter which we need to get from
the data table. So better to include both the columns in the local index.


Thanks,
Rajeshbabu.

On Thu, Feb 4, 2016 at 5:44 PM, Jacobo Coll <ja...@gmail.com> wrote:

> Hi Rajeshbabu,
>
> Thanks for the quick answer!
> I will keep an eye on that issue. I was expecting this to be working at
> least for local indexes.
>
> So, if the where condition refers to an indexed column and a non indexed
> column, it should use the index?
> I have tried this, and its not working for me.
>
> --------------------------------------------------
> create table test_table (mykey varchar primary key, col1 varchar, col2
> varchar);
> create local index idx2 on test_table (col2);
> upsert into test_table (mykey, col1, col2) values('k1', 'v1-1', 'v1-2');
> upsert into test_table (mykey, col1, col2) values('k2', 'v2-1', 'v2-2');
>
> -- select using the indexed column
> select * from test_table where col2 = 'v1-2';
> explain select * from test_table where col2 = 'v1-2';
>
> -- select using the indexed column and the non indexed column
> select * from test_table where col2 = 'v1-2' and col1 = 'v1-1';
> explain select * from test_table where col2 = 'v1-2' and col1 = 'v1-1';
> --------------------------------------------------
>
> The first select is using the index, but the second is not. I don't know if I am missing something.
>
> Thanks,
> Jacobo Coll
>
>
> 2016-02-04 9:13 GMT+00:00 rajeshbabu@apache.org <ch...@gmail.com>
> :
>
>> Hi Jacobo,
>> The local index will be used if you have any where condition on indexed
>> column otherwise we need to scan index table and data table for each row.
>> That's the reason why it's not using local indexes.
>>
>> There is no index merging currently in Phoenix. There is an improvement
>> task raised for it(PHOENIX-1801
>> <https://issues.apache.org/jira/browse/PHOENIX-1801>).
>>
>> Thanks,
>> Rajeshbabu.
>>
>> On Wed, Feb 3, 2016 at 5:31 PM, Jacobo Coll <ja...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I recently started testing HBase/Phoenix as a storage solution for our
>>> data. The problem is that I am not able to execute some "simple queries". I
>>> am using phoenix 4.5.2 and hbase 1.0.0-cdh5.4.0. After creating a table and
>>> making some selects (full scan), I started using local indexes to
>>> accelerate it. As I know, global indexes only work for covered queries, but:
>>>
>>> Unlike global indexes, local indexes *will* use an index even when all
>>>> columns referenced in the query are not contained in the index.
>>>>
>>>
>>> Knowing this, I prepared a simple test. A table with 3 columns: the
>>> primary key, a locally indexed column and a non indexed column.
>>> Querying by the indexed column works as expected, but if I try to use
>>> both columns, it does a full scan.
>>>
>>> I know that I can include both columns on the index, but this is not
>>> supposed to be required.
>>>
>>> In a previous mail I wrote a more detailed example of the test:
>>> http://mail-archives.apache.org/mod_mbox/phoenix-user/201512.mbox/%3CCAOJgazo-oCahZxsXrdOOsJxm-DVp7SzcNZhDDNJ2DWzkiNhfvA%40mail.gmail.com%3E
>>>
>>> Am I doing something wrong, or missing some step?
>>> Or it is impossible what I am trying to do?
>>> Is there some way to do this without include the other columns in the
>>> index?
>>>
>>> Thanks,
>>> Jacobo Coll
>>>
>>
>>
>

Re: Local index not used with non covered queries

Posted by Jacobo Coll <ja...@gmail.com>.
Hi Rajeshbabu,

Thanks for the quick answer!
I will keep an eye on that issue. I was expecting this to be working at
least for local indexes.

So, if the where condition refers to an indexed column and a non indexed
column, it should use the index?
I have tried this, and its not working for me.

--------------------------------------------------
create table test_table (mykey varchar primary key, col1 varchar, col2
varchar);
create local index idx2 on test_table (col2);
upsert into test_table (mykey, col1, col2) values('k1', 'v1-1', 'v1-2');
upsert into test_table (mykey, col1, col2) values('k2', 'v2-1', 'v2-2');

-- select using the indexed column
select * from test_table where col2 = 'v1-2';
explain select * from test_table where col2 = 'v1-2';

-- select using the indexed column and the non indexed column
select * from test_table where col2 = 'v1-2' and col1 = 'v1-1';
explain select * from test_table where col2 = 'v1-2' and col1 = 'v1-1';
--------------------------------------------------

The first select is using the index, but the second is not. I don't
know if I am missing something.

Thanks,
Jacobo Coll


2016-02-04 9:13 GMT+00:00 rajeshbabu@apache.org <ch...@gmail.com>:

> Hi Jacobo,
> The local index will be used if you have any where condition on indexed
> column otherwise we need to scan index table and data table for each row.
> That's the reason why it's not using local indexes.
>
> There is no index merging currently in Phoenix. There is an improvement
> task raised for it(PHOENIX-1801
> <https://issues.apache.org/jira/browse/PHOENIX-1801>).
>
> Thanks,
> Rajeshbabu.
>
> On Wed, Feb 3, 2016 at 5:31 PM, Jacobo Coll <ja...@gmail.com> wrote:
>
>> Hi,
>>
>> I recently started testing HBase/Phoenix as a storage solution for our
>> data. The problem is that I am not able to execute some "simple queries". I
>> am using phoenix 4.5.2 and hbase 1.0.0-cdh5.4.0. After creating a table and
>> making some selects (full scan), I started using local indexes to
>> accelerate it. As I know, global indexes only work for covered queries, but:
>>
>> Unlike global indexes, local indexes *will* use an index even when all
>>> columns referenced in the query are not contained in the index.
>>>
>>
>> Knowing this, I prepared a simple test. A table with 3 columns: the
>> primary key, a locally indexed column and a non indexed column.
>> Querying by the indexed column works as expected, but if I try to use
>> both columns, it does a full scan.
>>
>> I know that I can include both columns on the index, but this is not
>> supposed to be required.
>>
>> In a previous mail I wrote a more detailed example of the test:
>> http://mail-archives.apache.org/mod_mbox/phoenix-user/201512.mbox/%3CCAOJgazo-oCahZxsXrdOOsJxm-DVp7SzcNZhDDNJ2DWzkiNhfvA%40mail.gmail.com%3E
>>
>> Am I doing something wrong, or missing some step?
>> Or it is impossible what I am trying to do?
>> Is there some way to do this without include the other columns in the
>> index?
>>
>> Thanks,
>> Jacobo Coll
>>
>
>

Re: Local index not used with non covered queries

Posted by "rajeshbabu@apache.org" <ch...@gmail.com>.
Hi Jacobo,
The local index will be used if you have any where condition on indexed
column otherwise we need to scan index table and data table for each row.
That's the reason why it's not using local indexes.

There is no index merging currently in Phoenix. There is an improvement
task raised for it(PHOENIX-1801
<https://issues.apache.org/jira/browse/PHOENIX-1801>).

Thanks,
Rajeshbabu.

On Wed, Feb 3, 2016 at 5:31 PM, Jacobo Coll <ja...@gmail.com> wrote:

> Hi,
>
> I recently started testing HBase/Phoenix as a storage solution for our
> data. The problem is that I am not able to execute some "simple queries". I
> am using phoenix 4.5.2 and hbase 1.0.0-cdh5.4.0. After creating a table and
> making some selects (full scan), I started using local indexes to
> accelerate it. As I know, global indexes only work for covered queries, but:
>
> Unlike global indexes, local indexes *will* use an index even when all
>> columns referenced in the query are not contained in the index.
>>
>
> Knowing this, I prepared a simple test. A table with 3 columns: the
> primary key, a locally indexed column and a non indexed column.
> Querying by the indexed column works as expected, but if I try to use both
> columns, it does a full scan.
>
> I know that I can include both columns on the index, but this is not
> supposed to be required.
>
> In a previous mail I wrote a more detailed example of the test:
> http://mail-archives.apache.org/mod_mbox/phoenix-user/201512.mbox/%3CCAOJgazo-oCahZxsXrdOOsJxm-DVp7SzcNZhDDNJ2DWzkiNhfvA%40mail.gmail.com%3E
>
> Am I doing something wrong, or missing some step?
> Or it is impossible what I am trying to do?
> Is there some way to do this without include the other columns in the
> index?
>
> Thanks,
> Jacobo Coll
>