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\", \"info\".\"time\")";*
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?
Thanks,
Yixuan
