[jira] [Resolved] (PHOENIX-3516) Performance Issues with queries that have compound filters and specify phoenix.query.force.rowkeyorder=true

["Thomas D'Silva (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/PHOENIX-3516?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas D'Silva resolved PHOENIX-3516.
-------------------------------------
       Resolution: Fixed
    Fix Version/s: 4.10.0
                   4.9.1

> Performance Issues with queries that have compound filters and specify phoenix.query.force.rowkeyorder=true
> -----------------------------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-3516
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3516
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.9.0
>            Reporter: Jan Fernando
>            Assignee: Thomas D'Silva
>             Fix For: 4.9.1, 4.10.0
>
>         Attachments: PHOENIX-3516.patch
>
>
> On all our connections we specify the phoenix.query.force.rowkeyorder=true property to force serial scans so that we only support queries that will scale horizontally with data size. 
> In running performance tests, we found that queries with multiple AND'ed range filters were slow and not performing not as expected. We looked at the query plan and noticed that, in the slow query case, the query plan is doing a PARALLEL 1-WAY SCAN and doing a SERVER FILTER BY whereas the fast query is simply doing a SERIAL 1-WAY RANGE SCAN.
> We expect these queries to both have the same plan as we are specifying phoenix.query.force.rowkeyorder=true.
> You can repro as follows:
> 1. Use non-tenant specific connection to create the table:
> CREATE TABLE IF NOT EXISTS MY_MT_TABLE.TEST_TABLE (
>     ORGANIZATION_ID CHAR(15) NOT NULL,
>     PARENT_TYPE CHAR(3) NOT NULL,
>     PARENT_ID CHAR(15) NOT NULL,
>     CREATED_DATE DATE NOT NULL
>     CONSTRAINT PK PRIMARY KEY 
>     (
>         ORGANIZATION_ID, 
>         PARENT_TYPE,
>         PARENT_ID,
>         CREATED_DATE DESC
>     )
> ) VERSIONS=1,MULTI_TENANT=true,REPLICATION_SCOPE=1;
> 2. Use non-tenant specific connection to execute index:
> CREATE INDEX IF NOT EXISTS MY_TEST_TABLE_INDEX 
> ON MY_MT_TABLE.TEST_TABLE (PARENT_TYPE, CREATED_DATE, PARENT_ID);
> 3. Use a tenant-specific connection to create the View:
> CREATE VIEW IF NOT EXISTS MY_TEST_TABLE_VIEW AS SELECT * FROM MY_MT_TABLE.TEST_TABLE;
> 4. Run queries below with tenant-specific connection:
> Query with expected plan:
> EXPLAIN SELECT PARENT_ID
> FROM MY_TEST_TABLE_VIEW
> WHERE PARENT_TYPE='001'
> AND (CREATED_DATE > to_date('2012-10-21') AND CREATED_DATE < to_date('2016-10-31'))
> ORDER BY PARENT_TYPE,CREATED_DATE LIMIT 501;
> +------------------------------------------+
> |                   PLAN                   |
> +------------------------------------------+
> | CLIENT 1-CHUNK SERIAL 1-WAY RANGE SCAN OVER MY_MT_TABLE.MY_TEST_TABLE_INDEX ['00Dxx0000001gFA','001','2012-10-21 00:00:00.001'] - ['00Dxx0000001gFA','001',' |
> |     SERVER FILTER BY FIRST KEY ONLY      |
> |     SERVER 501 ROW LIMIT                 |
> | CLIENT 501 ROW LIMIT                     |
> +------------------------------------------+
> Slow query with unexpected plan. Since the date range are overlapping we expected Phoenix to consolidate this into a the smallest matching range and do a range scan. It does seem to do the consolidation but then do a parallel and not a range scan.
> EXPLAIN SELECT PARENT_ID
> FROM MY_TEST_TABLE_VIEW
> WHERE PARENT_TYPE='001'
> AND (CREATED_DATE >= to_date('2011-01-01') AND CREATED_DATE <= to_date('2016-01-01'))
> AND (CREATED_DATE > to_date('2012-10-21') AND CREATED_DATE < to_date('2016-10-31'))
> ORDER BY PARENT_TYPE,CREATED_DATE LIMIT 501;
> +------------------------------------------+
> |                   PLAN                   |
> +------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER MY_MT_TABLE.MY_TEST_TABLE_INDEX ['00Dxx0000001gFA','001','2012-10-21 00:00:00.001'] - ['00Dxx0000001gFA','001' |
> |     SERVER FILTER BY FIRST KEY ONLY AND (true AND true) |
> |     SERVER 501 ROW LIMIT                 |
> | CLIENT 501 ROW LIMIT                     |
> +------------------------------------------+



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)