phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kevin Verhoeven <Kevin.Verhoe...@ds-iq.com>
Subject Re: Index not used after successful creation
Date Thu, 29 Jan 2015 15:04:00 GMT
That will solve my problem, thanks so much! Is there an upgrade path, or do I simply overwrite
the 4.1 jars with the  newest jars and restart HBase?

Thanks,

Kevin

> On Jan 28, 2015, at 11:46 PM, James Taylor <jamestaylor@apache.org> wrote:
> 
> 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