[ https://issues.apache.org/jira/browse/PHOENIX-1645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14312584#comment-14312584 ]
Mujtaba Chohan commented on PHOENIX-1645: ----------------------------------------- That looks fine/inline with previous runs. > 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)