phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Index not used after successful creation
Date Thu, 29 Jan 2015 07:46:50 GMT
Hi Kevin,
This is a bug that has been fixed in 4.2.2. I tried the following to
verify. Does this reproduce your situation?
Thanks,
James

0: jdbc:phoenix:localhost> create table test.product(k varchar primary
key, f.upc integer, f.name varchar);
No rows affected (0.263 seconds)
0: jdbc:phoenix:localhost> CREATE INDEX IF NOT EXISTS
IX_PRODUCT_UPC_NAME ON TEST.PRODUCT (F.UPC, F.NAME);
No rows affected (5.327 seconds)
0: jdbc:phoenix:localhost> EXPLAIN SELECT F.UPC FROM TEST.PRODUCT
WHERE F.UPC=123456 and F.NAME ='A';
+------------------------------------------+
|                   PLAN                   |
+------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER
TEST.IX_PRODUCT_UPC_NAME [123,456,'A'] |
+------------------------------------------+
1 row selected (0.061 seconds)
0: jdbc:phoenix:localhost>

On Wed, Jan 28, 2015 at 4:06 PM, Kevin Verhoeven
<Kevin.Verhoeven@ds-iq.com> wrote:
> We are using Phoenix 4.1 with HBase 0.98.6-cdh5.2.0. After I successfully
> create an index and run an explain against my query that uses the index, the
> results state that a FULL SCAN is used against the index instead of a RANGE
> SCAN. What could be causing this problem? I would expect that a RANGE SCAN
> would be used against the index.
>
>
>
> For example:
>
>
>
> CREATE INDEX IF NOT EXISTS IX_PRODUCT_UPC_NAME ON TEST.PRODUCT (F.UPC,
> F.NAME);
>
> 2,277,977 rows affected (323.538 seconds)
>
>
>
> EXPLAIN SELECT F.UPC FROM TEST.PRODUCT WHERE F.UPC=123456 and F.NAME ='A';
>
>
>
> +------------+
>
> |    PLAN    |
>
> +------------+
>
> | CLIENT PARALLEL 32-WAY FULL SCAN OVER TEST.IX_PRODUCT_UPC_NAME |
>
> |     SERVER FILTER BY (TO_LONG(UPC) = 123456 AND NAME = 'A') |
>
> +------------+
>
> 2 rows selected (0.059 seconds)
>
>
>
>
>
> Even if I use a hint to force the query to use the index OR a range scan,
> the results are the same, a FULL SCAN is used. Any suggestions?
>
>
>
> For example:
>
>
>
> EXPLAIN SELECT /* INDEX(TEST.IX_PRODUCT_UPC_NAME) */ F.UPC FROM TEST.PRODUCT
> WHERE F.UPC=123456 and F.NAME ='A';
>
>
>
> +------------+
>
> |    PLAN    |
>
> +------------+
>
> | CLIENT PARALLEL 32-WAY FULL SCAN OVER TEST.IX_PRODUCT_UPC_NAME |
>
> |     SERVER FILTER BY (TO_LONG(UPC) = 123456 AND NAME = 'A') |
>
> +------------+
>
> 2 rows selected (0.056 seconds)
>
>
>
> Thank you for your time,
>
>
>
> Kevin Verhoeven
>
> kevin.verhoeven@ds-iq.com

Mime
View raw message