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)
