Apache Phoenix Performance Result :: Wed Aug 06 13:35:50 PDT 2014
Test Suite: INDEX_WIDEKEYS

Queries by Category

Index

select count(*) from LOCAL_INDEXED_TABLE_WIDE
0.36s 4.0-HBASE-0.98.1-HADOOP1

select /*+ INDEX(LOCAL_INDEXED_TABLE_WIDE lidx1 lidx2 lidx3 lidx4) */ count(*) from LOCAL_INDEXED_TABLE_WIDE
3.24s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ count(*) from LOCAL_INDEXED_TABLE_WIDE
1.31s 4.0-HBASE-0.98.1-HADOOP1

select round(date, 'day',50), sum(core)+2 from LOCAL_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.12s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from LOCAL_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
1.63s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from LOCAL_INDEXED_TABLE_WIDE where core IN (1,100)
0.02s 4.0-HBASE-0.98.1-HADOOP1

select count(NONINDEXED) from LOCAL_INDEXED_TABLE_WIDE where core IN (1,100)
SQL Exception - Check logs for 4.0-HBASE-0.98.1-HADOOP1

select count(*) from MUTABLE_INDEXED_TABLE_WIDE
0.72s 4.0-HBASE-0.98.1-HADOOP1

select /*+ INDEX(MUTABLE_INDEXED_TABLE_WIDE midx1 midx2 midx3 midx4) */ count(*) from MUTABLE_INDEXED_TABLE_WIDE
0.81s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ count(*) from MUTABLE_INDEXED_TABLE_WIDE
1.4s 4.0-HBASE-0.98.1-HADOOP1

select round(date, 'day',50), sum(core)+2 from MUTABLE_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.35s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from MUTABLE_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
2.04s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from MUTABLE_INDEXED_TABLE_WIDE where core IN (1,100)
0.06s 4.0-HBASE-0.98.1-HADOOP1

select count(NONINDEXED) from MUTABLE_INDEXED_TABLE_WIDE where core IN (1,100)
2.09s 4.0-HBASE-0.98.1-HADOOP1

select count(*) from IMMUTABLE_INDEXED_TABLE_WIDE
0.79s 4.0-HBASE-0.98.1-HADOOP1

select /*+ INDEX(IMMUTABLE_INDEXED_TABLE_WIDE idx1 idx2 idx3 idx4) */ count(*) from IMMUTABLE_INDEXED_TABLE_WIDE
0.78s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ count(*) from IMMUTABLE_INDEXED_TABLE_WIDE
1.54s 4.0-HBASE-0.98.1-HADOOP1

select round(date, 'day',50), sum(core)+2 from IMMUTABLE_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.36s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from IMMUTABLE_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
2.54s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from IMMUTABLE_INDEXED_TABLE_WIDE where core IN (1,100)
0.09s 4.0-HBASE-0.98.1-HADOOP1

select count(NONINDEXED) from IMMUTABLE_INDEXED_TABLE_WIDE where core IN (1,100)
2.47s 4.0-HBASE-0.98.1-HADOOP1

Index Creation After Data

CREATE INDEX midx5 ON MUTABLE_INDEXED_TABLE_WIDE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
138.27s 4.0-HBASE-0.98.1-HADOOP1

CREATE INDEX idx5 ON IMMUTABLE_INDEXED_TABLE_WIDE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
124.14s 4.0-HBASE-0.98.1-HADOOP1

Load Data

LOAD DATA LOCAL_INDEXED_TABLE_WIDE [3000000 ROWS]
2025.4s 4.0-HBASE-0.98.1-HADOOP1

LOAD DATA MUTABLE_INDEXED_TABLE_WIDE [3000000 ROWS]
3511.44s 4.0-HBASE-0.98.1-HADOOP1

LOAD DATA IMMUTABLE_INDEXED_TABLE_WIDE [3000000 ROWS]
1936.79s 4.0-HBASE-0.98.1-HADOOP1

Local Index Creation After Data

CREATE LOCAL INDEX lidx5 ON LOCAL_INDEXED_TABLE_WIDE (CORE, HOST)
SQL Exception - Check logs for 4.0-HBASE-0.98.1-HADOOP1



Queries by Table

LOCAL_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 3000000 [3M]

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, STATS.NONINDEXED INTEGER CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE)) MAX_FILESIZE=30485760;CREATE LOCAL INDEX lidx1 ON $TABLE (CORE);CREATE LOCAL INDEX lidx2 ON $TABLE (DB);CREATE LOCAL INDEX lidx3 ON $TABLE (DB,ACTIVE_VISITOR);CREATE LOCAL INDEX lidx4 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
INTEGER :: Max. Value: 10000 Values: RANDOM

select count(*) from LOCAL_INDEXED_TABLE_WIDE
0.36s 4.0-HBASE-0.98.1-HADOOP1

select /*+ INDEX(LOCAL_INDEXED_TABLE_WIDE lidx1 lidx2 lidx3 lidx4) */ count(*) from LOCAL_INDEXED_TABLE_WIDE
3.24s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ count(*) from LOCAL_INDEXED_TABLE_WIDE
1.31s 4.0-HBASE-0.98.1-HADOOP1

select round(date, 'day',50), sum(core)+2 from LOCAL_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.12s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from LOCAL_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
1.63s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from LOCAL_INDEXED_TABLE_WIDE where core IN (1,100)
0.02s 4.0-HBASE-0.98.1-HADOOP1

select count(NONINDEXED) from LOCAL_INDEXED_TABLE_WIDE where core IN (1,100)
SQL Exception - Check logs for 4.0-HBASE-0.98.1-HADOOP1

CREATE LOCAL INDEX lidx5 ON LOCAL_INDEXED_TABLE_WIDE (CORE, HOST)
SQL Exception - Check logs for 4.0-HBASE-0.98.1-HADOOP1

LOAD DATA LOCAL_INDEXED_TABLE_WIDE [3000000 ROWS]
2025.4s 4.0-HBASE-0.98.1-HADOOP1

MUTABLE_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 3000000 [3M]

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, STATS.NONINDEXED 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
INTEGER :: Max. Value: 10000 Values: RANDOM

select count(*) from MUTABLE_INDEXED_TABLE_WIDE
0.72s 4.0-HBASE-0.98.1-HADOOP1

select /*+ INDEX(MUTABLE_INDEXED_TABLE_WIDE midx1 midx2 midx3 midx4) */ count(*) from MUTABLE_INDEXED_TABLE_WIDE
0.81s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ count(*) from MUTABLE_INDEXED_TABLE_WIDE
1.4s 4.0-HBASE-0.98.1-HADOOP1

select round(date, 'day',50), sum(core)+2 from MUTABLE_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.35s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from MUTABLE_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
2.04s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from MUTABLE_INDEXED_TABLE_WIDE where core IN (1,100)
0.06s 4.0-HBASE-0.98.1-HADOOP1

select count(NONINDEXED) from MUTABLE_INDEXED_TABLE_WIDE where core IN (1,100)
2.09s 4.0-HBASE-0.98.1-HADOOP1

CREATE INDEX midx5 ON MUTABLE_INDEXED_TABLE_WIDE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
138.27s 4.0-HBASE-0.98.1-HADOOP1

LOAD DATA MUTABLE_INDEXED_TABLE_WIDE [3000000 ROWS]
3511.44s 4.0-HBASE-0.98.1-HADOOP1

IMMUTABLE_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 3000000 [3M]

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, STATS.NONINDEXED 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
INTEGER :: Max. Value: 10000 Values: RANDOM

select count(*) from IMMUTABLE_INDEXED_TABLE_WIDE
0.79s 4.0-HBASE-0.98.1-HADOOP1

select /*+ INDEX(IMMUTABLE_INDEXED_TABLE_WIDE idx1 idx2 idx3 idx4) */ count(*) from IMMUTABLE_INDEXED_TABLE_WIDE
0.78s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ count(*) from IMMUTABLE_INDEXED_TABLE_WIDE
1.54s 4.0-HBASE-0.98.1-HADOOP1

select round(date, 'day',50), sum(core)+2 from IMMUTABLE_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.36s 4.0-HBASE-0.98.1-HADOOP1

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from IMMUTABLE_INDEXED_TABLE_WIDE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
2.54s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from IMMUTABLE_INDEXED_TABLE_WIDE where core IN (1,100)
0.09s 4.0-HBASE-0.98.1-HADOOP1

select count(NONINDEXED) from IMMUTABLE_INDEXED_TABLE_WIDE where core IN (1,100)
2.47s 4.0-HBASE-0.98.1-HADOOP1

CREATE INDEX idx5 ON IMMUTABLE_INDEXED_TABLE_WIDE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
124.14s 4.0-HBASE-0.98.1-HADOOP1

LOAD DATA IMMUTABLE_INDEXED_TABLE_WIDE [3000000 ROWS]
1936.79s 4.0-HBASE-0.98.1-HADOOP1


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