Phoenix SNAPSHOT Performance

Apache Phoenix Performance Result :: Tue Feb 11 09:02:07 PST 2014

Queries by Category

Index

select count(*) from INDEXED_TABLE_MUTABLE

1.09s PHOENIX-2.2.3

0.96s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(*) from INDEXED_TABLE_MUTABLE

2.2s PHOENIX-2.2.3

2.45s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(*) from INDEXED_TABLE_MUTABLE

0.48s PHOENIX-2.2.3

0.51s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE_MUTABLE

1.57s PHOENIX-2.2.3

1.08s PHOENIX-SNAPSHOT

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host

0.25s PHOENIX-2.2.3

0.32s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host

1.47s PHOENIX-2.2.3

1.09s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)

0.16s PHOENIX-2.2.3

0.12s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)

0.08s PHOENIX-2.2.3

0.09s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)

0.1s PHOENIX-2.2.3

0.1s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)

1.07s PHOENIX-2.2.3

0.78s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)

0.12s PHOENIX-2.2.3

0.14s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)

0.13s PHOENIX-2.2.3

0.14s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)

0.11s PHOENIX-2.2.3

0.11s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)

1.1s PHOENIX-2.2.3

0.8s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200

0.25s PHOENIX-2.2.3

0.29s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200

0.28s PHOENIX-2.2.3

0.37s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200

0.22s PHOENIX-2.2.3

0.27s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200

1.38s PHOENIX-2.2.3

0.99s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core < 100 and db < 2000

0.62s PHOENIX-2.2.3

0.7s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core < 100 and db < 2000

2.95s PHOENIX-2.2.3

2.04s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core > 90

0.13s PHOENIX-2.2.3

0.16s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core > 90

1.13s PHOENIX-2.2.3

0.8s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core > 0

0.44s PHOENIX-2.2.3

0.57s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core > 0

1.91s PHOENIX-2.2.3

1.37s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE_MUTABLE where db >5 and db <7 order by db

0.11s PHOENIX-2.2.3

0.14s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE_MUTABLE where db >5 and db <7 order by db

1.77s PHOENIX-2.2.3

1.26s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE_MUTABLE order by db limit 10

0.78s PHOENIX-2.2.3

0.91s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE_MUTABLE order by db limit 10

2.52s PHOENIX-2.2.3

1.79s PHOENIX-SNAPSHOT

Index Creation After Data

CREATE INDEX midx5 ON INDEXED_TABLE_MUTABLE (CORE) INCLUDE (DB,ACTIVE_VISITOR)

31.64s PHOENIX-2.2.3

25.26s PHOENIX-SNAPSHOT

Load Data

LOAD DATA INDEXED_TABLE_MUTABLE [1000000 ROWS]

272.49s PHOENIX-2.2.3

1451.45s PHOENIX-SNAPSHOT

Queries by Table

INDEXED_TABLE_MUTABLE

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

ROWS 1000000 [1M]

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)) 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);CREATE INDEX mids1 ON $TABLE (CORE) SALT_BUCKETS=16;CREATE INDEX mids2 ON $TABLE (DB) SALT_BUCKETS=16;CREATE INDEX mids3 ON $TABLE (DB,ACTIVE_VISITOR) SALT_BUCKETS=16;CREATE INDEX mids4 ON $TABLE (CORE,DB,ACTIVE_VISITOR) SALT_BUCKETS=16;

DATA GENERATOR
STRING :: Values: NA|CS|EU
STRING :: Values: Salesforce.com|Apple.com|Google.com|Yelp.com
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

1.09s PHOENIX-2.2.3

0.96s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(*) from INDEXED_TABLE_MUTABLE

2.2s PHOENIX-2.2.3

2.45s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(*) from INDEXED_TABLE_MUTABLE

0.48s PHOENIX-2.2.3

0.51s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE_MUTABLE

1.57s PHOENIX-2.2.3

1.08s PHOENIX-SNAPSHOT

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host

0.25s PHOENIX-2.2.3

0.32s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host

1.47s PHOENIX-2.2.3

1.09s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)

0.16s PHOENIX-2.2.3

0.12s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)

0.08s PHOENIX-2.2.3

0.09s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)

0.1s PHOENIX-2.2.3

0.1s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)

1.07s PHOENIX-2.2.3

0.78s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)

0.12s PHOENIX-2.2.3

0.14s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)

0.13s PHOENIX-2.2.3

0.14s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)

0.11s PHOENIX-2.2.3

0.11s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)

1.1s PHOENIX-2.2.3

0.8s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200

0.25s PHOENIX-2.2.3

0.29s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200

0.28s PHOENIX-2.2.3

0.37s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200

0.22s PHOENIX-2.2.3

0.27s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200

1.38s PHOENIX-2.2.3

0.99s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core < 100 and db < 2000

0.62s PHOENIX-2.2.3

0.7s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core < 100 and db < 2000

2.95s PHOENIX-2.2.3

2.04s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core > 90

0.13s PHOENIX-2.2.3

0.16s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core > 90

1.13s PHOENIX-2.2.3

0.8s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core > 0

0.44s PHOENIX-2.2.3

0.57s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core > 0

1.91s PHOENIX-2.2.3

1.37s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE_MUTABLE where db >5 and db <7 order by db

0.11s PHOENIX-2.2.3

0.14s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE_MUTABLE where db >5 and db <7 order by db

1.77s PHOENIX-2.2.3

1.26s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE_MUTABLE order by db limit 10

0.78s PHOENIX-2.2.3

0.91s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE_MUTABLE order by db limit 10

2.52s PHOENIX-2.2.3

1.79s PHOENIX-SNAPSHOT

CREATE INDEX midx5 ON INDEXED_TABLE_MUTABLE (CORE) INCLUDE (DB,ACTIVE_VISITOR)

31.64s PHOENIX-2.2.3

25.26s PHOENIX-SNAPSHOT

LOAD DATA INDEXED_TABLE_MUTABLE [1000000 ROWS]

272.49s PHOENIX-2.2.3

1451.45s PHOENIX-SNAPSHOT

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