phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mujtaba Chohan (JIRA)" <j...@apache.org>
Subject [jira] [Created] (PHOENIX-4476) Range scan used for point lookups if filter is not in order of primary keys
Date Tue, 19 Dec 2017 23:39:00 GMT
Mujtaba Chohan created PHOENIX-4476:
---------------------------------------

             Summary: Range scan used for point lookups if filter is not in order of primary
keys
                 Key: PHOENIX-4476
                 URL: https://issues.apache.org/jira/browse/PHOENIX-4476
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 4.13.1
            Reporter: Mujtaba Chohan


{noformat}
DROP TABLE TEST;

CREATE TABLE IF NOT EXISTS TEST (
    PK1 CHAR(1) NOT NULL,
    PK2 VARCHAR NOT NULL,
    PK3 VARCHAR NOT NULL,
    PK4 UNSIGNED_LONG NOT NULL,
    PK5 VARCHAR NOT NULL,
    V1 VARCHAR,
    V2 VARCHAR,
    V3 UNSIGNED_LONG
    CONSTRAINT state_pk PRIMARY KEY (
          PK1,
          PK2,
          PK3,
          PK4,
          PK5
    )
);

// Incorrect explain plan with un-ordered PKs
EXPLAIN SELECT V1 FROM TEST WHERE (PK1, PK5, PK2, PK3, PK4) IN (('A', 'E', 'N', 'T', 3), ('A',
'Y', 'G', 'T', 4)); 

+------------------------------------------+------------------------------------------+------------------------------------------+---------+
|                   PLAN                   |              EST_BYTES_READ              |  
           EST_ROWS_READ               |         |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST ['A'] | null            
                        | null                   |
|     SERVER FILTER BY (PK1, PK5, PK2, PK3, PK4) IN ([65,69,0,78,0,84,0,0,0,0,0,0,0,0,3],[65,89,0,71,0,84,0,0,0,0,0,0,0,0,4])
| null       |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+

// Correct explain plan with PKs in order
EXPLAIN SELECT V1 FROM TEST WHERE (PK1,PK2,PK3,PK4,PK5) IN (('A', 'E', 'N',3, 'T'),('A', 'Y',
'G', 4, 'T')); 

+------------------------------------------+------------------------------------------+------------------------------------------+---------+
|                   PLAN                   |              EST_BYTES_READ              |  
           EST_ROWS_READ               |         |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+
| CLIENT 1-CHUNK 2 ROWS 712 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 2 KEYS OVER TEST
| 712                                      | |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+

{noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message