[jira] [Created] (PHOENIX-1645) Wrong execution plan generated for indexed query which leads to slow performance

["Mujtaba Chohan (JIRA)" <ji...@apache.org>]

Mujtaba Chohan created PHOENIX-1645:
---------------------------------------

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