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.verhoe...@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.verhoe...@ds-iq.com
