[
https://issues.apache.org/jira/browse/PHOENIX-3644?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
saikiran perumala updated PHOENIX-3644:
---------------------------------------
Description:
I was looking at explain plan for IN / OR operators in a where statements, I
got some conflicting results
Non tenant query :
here IN AND operator are on PK
Explain Select * from CUSTOM_ENTITY."CUSTOM_ENTITY_DATA_NO_ID" Where
Organization_id IN ('00Dxx0000001i28', '00Dxx0000001i29') AND Key_prefix =
'z0D';
Explain Select * from CUSTOM_ENTITY."CUSTOM_ENTITY_DATA_NO_ID" Where
(Organization_id = '00Dxx0000001i28' OR Organization_id = '00Dxx0000001i29')
AND Key_prefix = 'z0D';
Both give same result :
CLIENT PARALLEL 32-WAY POINT LOOKUP ON 2 KEYS OVER
CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
Tenant Specific View:
here IN AND operator are on PK
explain SELECT * FROM CUSTOM_ENTITY."z0D" WHERE C00NXX000001DIBOEAS
IN('ROW-THREAD_1-VAL-99999','ROW-THREAD_1-VAL-99998','ROW-THREAD_1-VAL-99997')
this is the query plan
CLIENT PARALLEL 32-WAY POINT LOOKUP ON 3 KEYS OVER
CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
SERVER FILTER BY PageFilter 100
SERVER 100 ROW LIMIT
CLIENT 100 ROW LIMIT
But when there is an OR say for this query
explain SELECT * FROM CUSTOM_ENTITY."z0D" WHERE
(C00NXX000001DIBUEAS='ROW-THREAD_1-VAL-99998' OR
C00NXX000001DIBUEAS='ROW-THREAD_1-VAL-99997')
This is the query plan :
CLIENT PARALLEL 32-WAY RANGE SCAN OVER CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
['00Dxx0000001i28','z0D']
SERVER FILTER BY (C00NXX000001DIBUEAS = 'ROW-THREAD_1-VAL-99998' OR
C00NXX000001DIBUEAS = 'ROW-THREAD_1-VAL-99997')
SERVER 100 ROW LIMIT
CLIENT 100 ROW LIMIT
In a tenant specific view IN and OR operators on a PK return different query
plan, OR filter is doing a full range scan instead of a Point query.
DDL :
CREATE TABLE IF NOT EXISTS CUSTOM_ENTITY.CUSTOM_TABLE (
ORGANIZATION_ID CHAR(15) NOT NULL,
KEY_PREFIX CHAR(3) NOT NULL,
CREATED_DATE DATE,
CREATED_BY CHAR(15),
SYSTEM_MODSTAMP DATE
CONSTRAINT PK PRIMARY KEY (
ORGANIZATION_ID,
KEY_PREFIX
)
) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1
DDL FOR VIEWS :
CREATE VIEW IF NOT EXISTS CUSTOM_VIEW."z0I" (
C00NXX000001DII4EAC VARCHAR(50) NOT NULL,
C00NXX000001DII3EAC CHAR(15),
C00NXX000001DII5EAC CHAR(15),
C00NXX000001DII6EAC DATE,
C00NXX000001DII7EAC DATE,
C00NXX000001DII8EAC DECIMAL,
C00NXX000001DII9EAC DECIMAL,
C00NXX000001DIIAEAS VARCHAR(100),
C00NXX000001DIIBEAS DECIMAL,
C00NXX000001DIICEAS DECIMAL,
C00NXX000001DIIDEAS DECIMAL,
C00NXX000001DIIEEAS VARCHAR(40),
C00NXX000001DIIFEAS VARCHAR(255),
C00NXX000001DIIGEAS VARCHAR(30),
C00NXX000001DIIHEAS VARCHAR(30),
C00NXX000001DIIIEAS VARCHAR(100),
C00NXX000001DIIJEAS VARCHAR(100),
C00NXX000001DIIKEAS VARCHAR(255),
C00NXX000001DIILEAS VARCHAR(255),
C00NXX000001DIIMEAS DECIMAL CONSTRAINT PK PRIMARY KEY
(C00NXX000001DII4EAC DESC)) AS SELECT * FROM CUSTOM_VIEW.CUSTOM_TABLE WHERE
KEY_PREFIX = 'z0I'
was:
I was looking at explain plan for IN / OR operators in a where statements, I
got some conflicting results
Non tenant query :
here IN AND operator are on PK
Explain Select * from CUSTOM_ENTITY."CUSTOM_ENTITY_DATA_NO_ID" Where
Organization_id IN ('00Dxx0000001i28', '00Dxx0000001i29') AND Key_prefix =
'z0D';
Explain Select * from CUSTOM_ENTITY."CUSTOM_ENTITY_DATA_NO_ID" Where
(Organization_id = '00Dxx0000001i28' OR Organization_id = '00Dxx0000001i29')
AND Key_prefix = 'z0D';
Both give same result :
CLIENT PARALLEL 32-WAY POINT LOOKUP ON 2 KEYS OVER
CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
Tenant Specific View:
here IN AND operator are on PK
explain SELECT * FROM CUSTOM_ENTITY."z0D" WHERE C00NXX000001DIBOEAS
IN('ROW-THREAD_1-VAL-99999','ROW-THREAD_1-VAL-99998','ROW-THREAD_1-VAL-99997')
this is the query plan
CLIENT PARALLEL 32-WAY POINT LOOKUP ON 3 KEYS OVER
CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
SERVER FILTER BY PageFilter 100
SERVER 100 ROW LIMIT
CLIENT 100 ROW LIMIT
But when there is an OR say for this query
explain SELECT * FROM CUSTOM_ENTITY."z0D" WHERE
(C00NXX000001DIBUEAS='ROW-THREAD_1-VAL-99998' OR
C00NXX000001DIBUEAS='ROW-THREAD_1-VAL-99997')
This is the query plan :
CLIENT PARALLEL 32-WAY RANGE SCAN OVER CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
['00Dxx0000001i28','z0D']
SERVER FILTER BY (C00NXX000001DIBUEAS = 'ROW-THREAD_1-VAL-99998' OR
C00NXX000001DIBUEAS = 'ROW-THREAD_1-VAL-99997')
SERVER 100 ROW LIMIT
CLIENT 100 ROW LIMIT
In a tenant specific view IN and OR operators on a PK return different query
plan, OR filter is doing a full range scan instead of a Point query.
DDL :
CREATE TABLE IF NOT EXISTS CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID (
ORGANIZATION_ID CHAR(15) NOT NULL,
KEY_PREFIX CHAR(3) NOT NULL,
CREATED_DATE DATE,
CREATED_BY CHAR(15),
SYSTEM_MODSTAMP DATE
CONSTRAINT PK PRIMARY KEY (
ORGANIZATION_ID,
KEY_PREFIX
)
) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1
> Phoenix Query With Multiple 'OR' operators does a full range scan when it is
> a tentant specific connection
> -----------------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-3644
> URL: https://issues.apache.org/jira/browse/PHOENIX-3644
> Project: Phoenix
> Issue Type: Bug
> Reporter: saikiran perumala
>
> I was looking at explain plan for IN / OR operators in a where statements, I
> got some conflicting results
> Non tenant query :
> here IN AND operator are on PK
> Explain Select * from CUSTOM_ENTITY."CUSTOM_ENTITY_DATA_NO_ID" Where
> Organization_id IN ('00Dxx0000001i28', '00Dxx0000001i29') AND Key_prefix =
> 'z0D';
> Explain Select * from CUSTOM_ENTITY."CUSTOM_ENTITY_DATA_NO_ID" Where
> (Organization_id = '00Dxx0000001i28' OR Organization_id = '00Dxx0000001i29')
> AND Key_prefix = 'z0D';
> Both give same result :
> CLIENT PARALLEL 32-WAY POINT LOOKUP ON 2 KEYS OVER
> CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
> Tenant Specific View:
> here IN AND operator are on PK
> explain SELECT * FROM CUSTOM_ENTITY."z0D" WHERE C00NXX000001DIBOEAS
> IN('ROW-THREAD_1-VAL-99999','ROW-THREAD_1-VAL-99998','ROW-THREAD_1-VAL-99997')
> this is the query plan
> CLIENT PARALLEL 32-WAY POINT LOOKUP ON 3 KEYS OVER
> CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
> SERVER FILTER BY PageFilter 100
> SERVER 100 ROW LIMIT
> CLIENT 100 ROW LIMIT
> But when there is an OR say for this query
> explain SELECT * FROM CUSTOM_ENTITY."z0D" WHERE
> (C00NXX000001DIBUEAS='ROW-THREAD_1-VAL-99998' OR
> C00NXX000001DIBUEAS='ROW-THREAD_1-VAL-99997')
> This is the query plan :
> CLIENT PARALLEL 32-WAY RANGE SCAN OVER CUSTOM_ENTITY.CUSTOM_ENTITY_DATA_NO_ID
> ['00Dxx0000001i28','z0D']
> SERVER FILTER BY (C00NXX000001DIBUEAS = 'ROW-THREAD_1-VAL-99998' OR
> C00NXX000001DIBUEAS = 'ROW-THREAD_1-VAL-99997')
> SERVER 100 ROW LIMIT
> CLIENT 100 ROW LIMIT
> In a tenant specific view IN and OR operators on a PK return different query
> plan, OR filter is doing a full range scan instead of a Point query.
> DDL :
> CREATE TABLE IF NOT EXISTS CUSTOM_ENTITY.CUSTOM_TABLE (
> ORGANIZATION_ID CHAR(15) NOT NULL,
> KEY_PREFIX CHAR(3) NOT NULL,
> CREATED_DATE DATE,
> CREATED_BY CHAR(15),
> SYSTEM_MODSTAMP DATE
> CONSTRAINT PK PRIMARY KEY (
> ORGANIZATION_ID,
> KEY_PREFIX
> )
> ) VERSIONS=1, MULTI_TENANT=true, IMMUTABLE_ROWS=TRUE, REPLICATION_SCOPE=1
> DDL FOR VIEWS :
> CREATE VIEW IF NOT EXISTS CUSTOM_VIEW."z0I" (
> C00NXX000001DII4EAC VARCHAR(50) NOT NULL,
> C00NXX000001DII3EAC CHAR(15),
> C00NXX000001DII5EAC CHAR(15),
> C00NXX000001DII6EAC DATE,
> C00NXX000001DII7EAC DATE,
> C00NXX000001DII8EAC DECIMAL,
> C00NXX000001DII9EAC DECIMAL,
> C00NXX000001DIIAEAS VARCHAR(100),
> C00NXX000001DIIBEAS DECIMAL,
> C00NXX000001DIICEAS DECIMAL,
> C00NXX000001DIIDEAS DECIMAL,
> C00NXX000001DIIEEAS VARCHAR(40),
> C00NXX000001DIIFEAS VARCHAR(255),
> C00NXX000001DIIGEAS VARCHAR(30),
> C00NXX000001DIIHEAS VARCHAR(30),
> C00NXX000001DIIIEAS VARCHAR(100),
> C00NXX000001DIIJEAS VARCHAR(100),
> C00NXX000001DIIKEAS VARCHAR(255),
> C00NXX000001DIILEAS VARCHAR(255),
> C00NXX000001DIIMEAS DECIMAL CONSTRAINT PK PRIMARY KEY
> (C00NXX000001DII4EAC DESC)) AS SELECT * FROM CUSTOM_VIEW.CUSTOM_TABLE WHERE
> KEY_PREFIX = 'z0I'
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
