Hi,
I have a table in hbase and created a view of it in phoenix, along
with a local index:
create view "traces" (rowkey VARCHAR PRIMARY KEY, "cf"."time" VARCHAR,
"cf"."number" VARCHAR, "cf"."class" VARCHAR);
create local index idxf on "dmstab_vehicle_traces" ("cf"."number",
"cf"."class", rowkey);
I need to filter rows based on a regex condition on the "number"
column, so I use queries like these:
explain select * from "traces" where regexp_substr("number", 'Q.*') = "number";
CLIENT 2-CHUNK PARALLEL 2-WAY ROUND ROBIN RANGE SCAN OVER traces [2]
SERVER FILTER BY FIRST KEY ONLY AND REGEXP_SUBSTR("number", 'Q.*',
1) = "number"
It's a little ugly and less efficient than using LIKE, but the
performance is still relatively acceptable thanks to the index.
However, if I want to range of rowkeys to include, Phoenix stops using
the index, which slows down the query significantly:
explain select * from "traces" where regexp_substr("number", 'Q.*') =
"number" and rowkey < 'BY';
CLIENT 4-CHUNK 687413 ROWS 629145681 BYTES PARALLEL 1-WAY ROUND ROBIN
RANGE SCAN OVER traces [*] - ['BY']
SERVER FILTER BY REGEXP_SUBSTR(cf."number", 'Q.*', 1) = cf."number"
Using an index hint doesn't change anything. Is there a way to make
this work, and is this a bug?
