phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-1645) Wrong execution plan generated for indexed query which leads to slow performance
Date Sat, 07 Feb 2015 17:21:35 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-1645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14310828#comment-14310828
] 

James Taylor commented on PHOENIX-1645:
---------------------------------------

When hinted, we use the first index listed that is possible to use, not the
best among the ones listed. The idea is that the user is telling us that he
knows better.

Mujtaba - do we need to have these hints?


> 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
>
> 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)

Mime
View raw message