phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chinmay Kulkarni (Jira)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-5796) Possible query optimization when projecting uncovered columns and querying on indexed columns
Date Wed, 25 Mar 2020 18:25:00 GMT

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

Chinmay Kulkarni updated PHOENIX-5796:
--------------------------------------
    Labels: query-optimization  (was: )

> 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
>              Labels: query-optimization
>         Attachments: Screen Shot 2020-03-23 at 3.25.38 PM.png, Screen Shot 2020-03-23
at 3.32.24 PM.png, Screen Shot 2020-03-24 at 11.51.12 AM.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)

Mime
View raw message