[
https://issues.apache.org/jira/browse/PHOENIX-1645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14312571#comment-14312571
]
James Taylor commented on PHOENIX-1645:
---------------------------------------
How does perf look without the hint? Just want to make sure there's no perf
regression hiding behind this.
> Wrong execution plan generated for indexed query which leads to slow
> performance
> --------------------------------------------------------------------------------
>
> Key: PHOENIX-1645
> URL: https://issues.apache.org/jira/browse/PHOENIX-1645
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.3
> Reporter: Mujtaba Chohan
> Assignee: James Taylor
> Fix For: 4.3
>
>
> Query: select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(core)
> from INDEXED_TABLE where core < 10 and db < 200
> Optimal explain plan generated in Phoenix v4.2: 1-CHUNK PARALLEL 1-WAY RANGE
> SCAN OVER IDX4 [*] - [10]
> SERVER FILTER BY TO_LONG(DB) < 200
> SERVER AGGREGATE INTO SINGLE ROW
> *Wrong plan generated in 4.3 that uses skip scan join to base table.
> Performance of this plan compared to v4.2 is close to 20X slower with 2M rows
> data*: CLIENT 28-CHUNK PARALLEL 1-WAY FULL SCAN OVER INDEXED_TABLE
> SERVER FILTER BY USAGE.DB < 200
> SERVER AGGREGATE INTO SINGLE ROW
> SKIP-SCAN-JOIN TABLE 0
> CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER IDX1 [*] - [10]
> SERVER FILTER BY FIRST KEY ONLY
> DYNAMIC SERVER FILTER BY ("HOST", "DOMAIN", "FEATURE", "DATE") IN
> (($22.$24, $22.$25, $22.$26, $22.$27))
>
> DDL: CREATE TABLE $TABLE (HOST CHAR(2) NOT NULL,DOMAIN VARCHAR NOT
> NULL,FEATURE VARCHAR NOT NULL,DATE DATE NOT NULL,USAGE.CORE BIGINT,USAGE.DB
> BIGINT,STATS.ACTIVE_VISITOR INTEGER CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN,
> FEATURE, DATE)) IMMUTABLE_ROWS=true,MAX_FILESIZE=30485760;CREATE INDEX idx1
> ON $TABLE (CORE);CREATE INDEX idx2 ON $TABLE (DB);CREATE INDEX idx3 ON $TABLE
> (DB,ACTIVE_VISITOR);CREATE INDEX idx4 ON $TABLE
> (CORE,DB,ACTIVE_VISITOR);CREATE INDEX ids1 ON $TABLE (CORE)
> SALT_BUCKETS=16;CREATE INDEX ids2 ON $TABLE (DB) SALT_BUCKETS=16;CREATE INDEX
> ids3 ON $TABLE (DB,ACTIVE_VISITOR) SALT_BUCKETS=16;CREATE INDEX ids4 ON
> $TABLE (CORE,DB,ACTIVE_VISITOR) SALT_BUCKETS=16;
> Also see perf. run at:
> http://phoenix-bin.github.io/client/performance/phoenix-20150206042353.htm
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
