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

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

Kshitij Kulshrestha created PHOENIX-5143:
--------------------------------------------

             Summary: 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


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