phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Job Thomas" <>
Subject RE: Secondary index is not used
Date Sat, 03 May 2014 08:53:39 GMT
In Phoenix if you select any column apart from indexed column it will perform a full scan
to get the resut.
But you can include required columns in the same index created.
If you don't want to include column in the index table due to space utilization or dynamic
query , you can perform a work arouond for this.
Get the primary key from indexed table and using that key query the main table to get required
columns. once phoenix supports subquery we can achive the same in one subquery.
Thanks & Regards
Job M Thomas 


From: yixuan geng []
Sent: Sat 5/3/2014 1:03 PM
Subject: Secondary index is not used

Hi all, 

I am using the great secondary index feature on an existing immutable table. I was able to
successfully create the index and actually saw the index table in hbase.
However, when I do "explain {query}", I always get " x-way full scan" which I believe means
the index is not actually used.

I know people have asked similar questions and the root cause turned out to be that the columns
used in the query is not a subset of the columns defined/included in the index. But I am pretty
sure my index has covered all the columns in the query.

Here is the example:

I have an existing table in hbase named "metadata_test", it has only one column family "info".
There are already some data rows in the table.

1. Since the table is "write once, append only", I did:

ALTER TABLE \"sgtrack_metadata_test\" SET IMMUTABLE_ROWS=true

2. Then I created an index (info.appid, info.counterid, info.time) on this table as following:

create index \"test_index\" on \"metadata_test\" ( \"info\".\"appid\",\"info\".\"counterid\",

3. It took some time to create the index. Once its done, I saw a TEST_INDEX table in hbase.
So I ran a query to see if the index is actually used

explain select \"info\".\"appid\" from \"metadata_test\" where \"info\".\"appid\" = 'test_app'
and \"info\".\"counterid\" = 'test_counter';

Please note in the query only two columns (info.appid and info.counterid) are used

4. The result I got from "explain" is 

CLIENT PARALLEL 1-WAY FULL SCAN OVER sgtrack_metadata_test 

    SERVER FILTER BY (info.appid = 'test_app' AND info.counterid = 'test_counter'') 

I assume the explanation means the index is not used for the query.  I don't understand why.
 The only reason I can think of is my test dataset is small so Phoenix did not bother to use
the index. I tested with 50K rows and 3 million rows, both return the same full scan result.

I am using phoenix 2.2.2 client/server and hbase 0.94.16.

Anyone has any idea please?



View raw message