phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "saikiran perumala (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-3644) Phoenix Query With Multiple 'OR' operators does a full range scan when it is a tentant specific connection
Date Wed, 01 Feb 2017 22:54:52 GMT

     [ 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)

Mime
View raw message