[
https://issues.apache.org/jira/browse/PHOENIX-4476?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16566331#comment-16566331
]
Xu Cang commented on PHOENIX-4476:
----------------------------------
{quote}There are 3 possible ways to optimize this:
1. change getKeyRange algorithm in WhereOptimizer.java
or
2 re-order child expressions for expression object in #compile in
WhereCompiler.java
or
3 re-order child nodes in ParseNode in #compile in WhereCopiler.java.
{quote}
I tried the first approach. It can make the query plan to be point lookup but
actually, it's not correct because the constructed key is in the wrong order
which cannot be used for lookup.
So, we have to re-order "ParseNode" or "Expression" for the "where".
> 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
> Assignee: Xu Cang
> Priority: Major
> Labels: SFDC
> Attachments: PHOENIX-4476-4.x-HBase-1.3.002.patch
>
>
> {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
(v7.6.3#76005)
