You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Jan Fernando (JIRA)" <ji...@apache.org> on 2016/12/03 01:02:33 UTC
[jira] [Commented] (PHOENIX-3516) Performance Issues with queries
that have compound filters and specify phoenix.query.force.rowkeyorder=true
[ https://issues.apache.org/jira/browse/PHOENIX-3516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15717035#comment-15717035 ]
Jan Fernando commented on PHOENIX-3516:
---------------------------------------
One thing I forgot to mention is that the view is a tenant-specific view and all the queries are run using a tenant specific connection and adding the phoenix.query.force.rowkeyorder=true property to the JDBC url.
> 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)