[
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_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
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.
> 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_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
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
