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

["Jan Fernando (JIRA)" <ji...@apache.org>]

Jan Fernando created PHOENIX-3516:
-------------------------------------

             Summary: 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


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:

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;

CREATE INDEX IF NOT EXISTS MY_TEST_TABLE_INDEX 
ON MY_MT_TABLE.TEST_TABLE (PARENT_TYPE, CREATED_DATE, PARENT_ID);

CREATE VIEW IF NOT EXISTS MY_TEST_TABLE_VIEW AS SELECT * FROM MY_MT_TABLE.TEST_TABLE;

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)