phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marcin Januszkiewicz <katamaran...@gmail.com>
Subject Phoenix ignoring index and index hint with some queries over mapped hbase tables.
Date Fri, 03 Mar 2017 08:59:51 GMT
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?

Mime
View raw message