Apache Phoenix Performance Result :: Fri Mar 07 17:16:07 PST 2014
Queries by Category

Index

select count(*) from INDEXED_TABLE_WIDE
0.47s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+ INDEX(INDEXED_TABLE_WIDE idx1 idx2 idx3 idx4) */ count(*) from INDEXED_TABLE_WIDE
0.32s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE_WIDE
0.52s BEFORE-PHOENIX-113-COMMIT-C3F2

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.53s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
1.11s BEFORE-PHOENIX-113-COMMIT-C3F2

select count(core) from INDEXED_TABLE_WIDE where core IN (1,100)
0.06s BEFORE-PHOENIX-113-COMMIT-C3F2

select count(*) from INDEXED_TABLE_MUTABLE_WIDE
1.13s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+ INDEX(INDEXED_TABLE_MUTABLE_WIDE midx1 midx2 midx3 midx4) */ count(*) from INDEXED_TABLE_MUTABLE_WIDE
0.91s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE_MUTABLE_WIDE
0.55s BEFORE-PHOENIX-113-COMMIT-C3F2

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.31s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.88s BEFORE-PHOENIX-113-COMMIT-C3F2

select count(core) from INDEXED_TABLE_MUTABLE_WIDE where core IN (1,100)
0.05s BEFORE-PHOENIX-113-COMMIT-C3F2

Index Creation After Data

CREATE INDEX idx5 ON INDEXED_TABLE_WIDE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
65.74s BEFORE-PHOENIX-113-COMMIT-C3F2

CREATE INDEX midx5 ON INDEXED_TABLE_MUTABLE_WIDE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
66.34s BEFORE-PHOENIX-113-COMMIT-C3F2

Load Data

LOAD DATA INDEXED_TABLE_WIDE [2000000 ROWS]
442.98s BEFORE-PHOENIX-113-COMMIT-C3F2

LOAD DATA INDEXED_TABLE_MUTABLE_WIDE [2000000 ROWS]
1305.9s BEFORE-PHOENIX-113-COMMIT-C3F2



Queries by Table

INDEXED_TABLE_WIDE


Indexing test. Phoenix 2.0+ feature. See explain plan by hovering over version label to see which index table is used.

ROWS 2000000 [2M]

DDL CREATE TABLE $TABLE (HOST CHAR(2) NOT NULL,DOMAIN char(200) 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);

DATA GENERATOR
STRING :: Values: NA|CS|EU
STRING :: Length: 200 Values: RANDOM
STRING :: Values: Login|Report|Dashboard|Sales|UI
DATE :: Values: SEQUENTIAL
INTEGER :: Max. Value: 100 Values: RANDOM
INTEGER :: Max. Value: 2000 Values: RANDOM
INTEGER :: Max. Value: 10000 Values: RANDOM

select count(*) from INDEXED_TABLE_WIDE
0.47s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+ INDEX(INDEXED_TABLE_WIDE idx1 idx2 idx3 idx4) */ count(*) from INDEXED_TABLE_WIDE
0.32s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE_WIDE
0.52s BEFORE-PHOENIX-113-COMMIT-C3F2

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.53s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
1.11s BEFORE-PHOENIX-113-COMMIT-C3F2

select count(core) from INDEXED_TABLE_WIDE where core IN (1,100)
0.06s BEFORE-PHOENIX-113-COMMIT-C3F2

CREATE INDEX idx5 ON INDEXED_TABLE_WIDE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
65.74s BEFORE-PHOENIX-113-COMMIT-C3F2

LOAD DATA INDEXED_TABLE_WIDE [2000000 ROWS]
442.98s BEFORE-PHOENIX-113-COMMIT-C3F2

INDEXED_TABLE_MUTABLE_WIDE


Indexing test. Phoenix 2.0+ feature. See explain plan by hovering over version label to see which index table is used.

ROWS 2000000 [2M]

DDL CREATE TABLE $TABLE (HOST CHAR(2) NOT NULL,DOMAIN char(200) 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)) MAX_FILESIZE=30485760;CREATE INDEX midx1 ON $TABLE (CORE);CREATE INDEX midx2 ON $TABLE (DB);CREATE INDEX midx3 ON $TABLE (DB,ACTIVE_VISITOR);CREATE INDEX midx4 ON $TABLE (CORE,DB,ACTIVE_VISITOR);

DATA GENERATOR
STRING :: Values: NA|CS|EU
STRING :: Length: 200 Values: RANDOM
STRING :: Values: Login|Report|Dashboard|Sales|UI
DATE :: Values: SEQUENTIAL
INTEGER :: Max. Value: 100 Values: RANDOM
INTEGER :: Max. Value: 2000 Values: RANDOM
INTEGER :: Max. Value: 10000 Values: RANDOM

select count(*) from INDEXED_TABLE_MUTABLE_WIDE
1.13s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+ INDEX(INDEXED_TABLE_MUTABLE_WIDE midx1 midx2 midx3 midx4) */ count(*) from INDEXED_TABLE_MUTABLE_WIDE
0.91s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE_MUTABLE_WIDE
0.55s BEFORE-PHOENIX-113-COMMIT-C3F2

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.31s BEFORE-PHOENIX-113-COMMIT-C3F2

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.88s BEFORE-PHOENIX-113-COMMIT-C3F2

select count(core) from INDEXED_TABLE_MUTABLE_WIDE where core IN (1,100)
0.05s BEFORE-PHOENIX-113-COMMIT-C3F2

CREATE INDEX midx5 ON INDEXED_TABLE_MUTABLE_WIDE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
66.34s BEFORE-PHOENIX-113-COMMIT-C3F2

LOAD DATA INDEXED_TABLE_MUTABLE_WIDE [2000000 ROWS]
1305.9s BEFORE-PHOENIX-113-COMMIT-C3F2


Note: Hover over version label to see Phoenix Query Explain Plan.