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:06:51 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_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)

Mime
View raw message