[PHOENIX-5796] Possible query optimization when projecting uncovered columns and querying on indexed columns

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 5.0.0, 4.15.0
• Labels: query-optimization
• Assignee: Unassigned
• Reporter: Chinmay Kulkarni
• Created: 23/Mar/20 22:26
• Updated: 26/Mar/20 15:25

Description

Start HBase-1.3 server with Phoenix-4.15.0-HBase-1.3 server jar. Connect to it using sqlline.py which has Phoenix-4.15.0-HBase-1.3 Phoenix client.

Create a base table like:

create table t (a integer primary key, b varchar(10), c integer);

Create an uncovered index on top of it like:

create index uncov_index_t on t(b);

Now if you issue the query:

explain select c from t where b='abc';

You'd see the following explain plan:

Which is a full table scan on the base table 't' since we cannot use the global index as 'c' is not a covered column in the global index.

However, projecting columns contained fully within the index pk is correctly a range scan:

explain select a,b from t where b='abc';

produces the following explain plan:

In the first query, can there be an optimization to query the index table, get the start and stop keys of the base table and then issue a range scan/(bunch of point lookups) on the base table instead of doing a full table scan on the base table like we currently do?