You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "saikiran perumala (JIRA)" <ji...@apache.org> on 2017/02/01 22:03:51 UTC
[jira] [Created] (PHOENIX-3644) Phoenix Query With Multiple 'OR'
operators does a full range scan when it is a tentant specific connection
saikiran perumala created PHOENIX-3644:
------------------------------------------
Summary: 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.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)