phoenix-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kshitij Kulshrestha (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-5143) Support Range Scan for all columns with secondary global index without creating covered index
Date Tue, 19 Feb 2019 09:44:00 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-5143?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16771766#comment-16771766
] 

Kshitij Kulshrestha commented on PHOENIX-5143:
----------------------------------------------

 
{noformat}
EXPLAIN SELECT /*+ INDEX(INDEX_OPT MY_INDEX) */ * FROM INDEX_OPT WHERE MAIN_KEY = ( SELECT
MAIN_KEY FROM INDEX_OPT WHERE ALERT_ID ='1' )
{noformat}
May be I'm doing something wrong, but it does seems like even hints not working, it's doing
a FULL SCAN
{noformat}
CLIENT 1-CHUNK 200 ROWS 43000 BYTES SERIAL 1-WAY ROUND ROBIN FULL SCAN OVER TEST_DB:INDEX_OPT
SERVER 200 ROW LIMIT CLIENT 200 ROW LIMIT EXECUTE SINGLE-ROW SUBQUERY CLIENT 1-CHUNK 2 ROWS
90 BYTES SERIAL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST_DB:MY_INDEX33 ['1'] SERVER FILTER BY
FIRST KEY ONLY SERVER 2 ROW LIMIT CLIENT 2 ROW LIMIT
{noformat}
 

> Support Range Scan for all columns with secondary global index without creating covered
index
> ---------------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-5143
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-5143
>             Project: Phoenix
>          Issue Type: Improvement
>    Affects Versions: 5.0.0
>            Reporter: Kshitij Kulshrestha
>            Priority: Major
>
>  
> {code:java}
> CREATE TABLE IF NOT EXISTS INDEX_OPT (
> MAIN_KEY VARCHAR(32) NOT NULL,
> ALERT_ID VARCHAR(32),
> ALERT_TYPE VARCHAR(32)
> CONSTRAINT PK PRIMARY KEY (MAIN_KEY)
> )
> {code}
>  
>  
> {noformat}
> --> WITHOUT SECONDARY GLOBAL INDEX ON ALERT_ID
> EXPLAIN SELECT * FROM INDEX_OPT WHERE ALERT_ID = '1'
> CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER TEST_DB:INDEX_OPT
>  SERVER FILTER BY ALERT_ID = '1'
>  SERVER 200 ROW LIMIT
> CLIENT 200 ROW LIMIT
> {noformat}
>  
>  
> {noformat}
> --> WITH SECONDARY GLOBAL INDEX ON ALERT_ID
> CREATE INDEX MY_INDEX ON INDEX_OPT (ALERT_ID)
> EXPLAIN SELECT * FROM INDEX_OPT WHERE ALERT_ID = '1'
>  
> CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER TEST_DB:INDEX_OPT
>  SERVER FILTER BY ALERT_ID = '1'
>  SERVER 200 ROW LIMIT
> CLIENT 200 ROW LIMIT
> {noformat}
>  
>  
> As we can even though we have created an index on ALERT_ID but it does still shows FULL
SCAN if we select all columns, although if we select only the PRIMARY KEY, it does RANGE
SCAN
>  
> {noformat}
> QUERY 1
> EXPLAIN SELECT MAIN_KEY FROM INDEX_OPT WHERE ALERT_ID = '1'
> CLIENT 1-CHUNK 200 ROWS 9000 BYTES SERIAL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST_DB:MY_INDEX33
['1']
>  SERVER FILTER BY FIRST KEY ONLY
>  SERVER 200 ROW LIMIT
> CLIENT 200 ROW LIMIT
> {noformat}
>  
>  
>  
> {noformat}
> QUERY 2
> EXPLAIN SELECT * FROM INDEX_OPT WHERE MAIN_KEY = '1'
> CLIENT 1-CHUNK 1 ROWS 215 BYTES SERIAL 1-WAY ROUND ROBIN POINT LOOKUP ON 1 KEY OVER TEST_DB:INDEX_OPT
>  SERVER 200 ROW LIMIT
> CLIENT 200 ROW LIMIT
> {noformat}
>  
>  
> If we look at query 1 and query 2 they both are not doing FULL SCAN, but If I write a
query
>  
> {noformat}
> EXPLAIN
> SELECT * FROM INDEX_OPT WHERE MAIN_KEY = (
> SELECT MAIN_KEY FROM INDEX_OPT WHERE ALERT_ID ='1'
> )
> {noformat}
>  
> {noformat}
> CLIENT 1-CHUNK 200 ROWS 43000 BYTES SERIAL 1-WAY ROUND ROBIN FULL SCAN OVER TEST_DB:INDEX_OPT
SERVER 200 ROW LIMIT CLIENT 200 ROW LIMIT EXECUTE SINGLE-ROW SUBQUERY CLIENT 1-CHUNK 2 ROWS
90 BYTES SERIAL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST_DB:MY_INDEX33 ['1'] SERVER FILTER BY
FIRST KEY ONLY SERVER 2 ROW LIMIT CLIENT 2 ROW LIMIT{noformat}
> It's doing a full scan for the MAIN_KEY ?
> Instead he could have done a RANGE_SCAN



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message