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

["Chinmay Kulkarni (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/PHOENIX-5796?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Chinmay Kulkarni updated PHOENIX-5796:
--------------------------------------
    Summary: Possible query optimization when projecting uncovered columns and querying on indexed columns  (was: Possible query optimization when query projects uncovered columns and queries on indexed columns)

> Possible query optimization when projecting uncovered columns and querying on indexed columns
> ---------------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-5796
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-5796
>             Project: Phoenix
>          Issue Type: Improvement
>    Affects Versions: 5.0.0, 4.15.0
>            Reporter: Chinmay Kulkarni
>            Priority: Major
>         Attachments: Screen Shot 2020-03-23 at 3.25.38 PM.png, Screen Shot 2020-03-23 at 3.32.24 PM.png
>
>
> 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:
> {code:sql}
> create table t (a integer primary key, b varchar(10), c integer);
> {code}
> Create an uncovered index on top of it like:
> {code:sql}
> create index uncov_index_t on t(b);
> {code}
> Now if you issue the query:
> {code:sql}
> explain select c from t where b='abc';
> {code}
> You'd see the following explain plan:
>  !Screen Shot 2020-03-23 at 3.25.38 PM.png|height=150,width=700!
> *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:*
> {code:sql}
> explain select a,b from t where b='abc';
> {code}
> produces the following explain plan:
>  !Screen Shot 2020-03-23 at 3.32.24 PM.png|height=150,width=700! 
> 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?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)