[jira] [Comment Edited] (PHOENIX-5143) Support Range Scan for all columns with secondary global index without creating covered index

["Kshitij Kulshrestha (JIRA)" <ji...@apache.org>]

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

Kshitij Kulshrestha edited comment on PHOENIX-5143 at 2/19/19 9:43 AM:
-----------------------------------------------------------------------

 
{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}
 


was (Author: horizon23):
 
{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)