Phoenix SNAPSHOT Performance

Apache Phoenix Performance Result :: Fri Apr 18 17:08:04 PDT 2014

Test Suite: AGGREGATION

Queries by Category

Aggregation

select count(*) from AGG_TABLE

0.5s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(1) from AGG_TABLE group by HOST

0.54s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, count(1) from AGG_TABLE group by CORE

0.66s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

0.74s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, host, sum(ACTIVE_VISITOR) from AGG_TABLE group by core, host

3.17s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select AVG(DB), MIN(CORE), MAX(ACTIVE_VISITOR) from AGG_TABLE where host='CS' and DOMAIN<>'Google.com'

3.81s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

4s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

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

1.19s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.22s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select trunc(date, 'hour', 1000), count(DB), count(ACTIVE_VISITOR) from AGG_TABLE where feature='Login' and (core<50 or db<20 or active_visitor > 200) group by trunc(date, 'hour', 1000)

1.9s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.94s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(active_visitor) count from AGG_TABLE

2.81s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

2.76s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(distinct active_visitor) count from AGG_TABLE

3.12s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.12s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(core), count(db), count(active_visitor) from AGG_TABLE

3.7s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.72s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, count(db) count from AGG_TABLE group by CORE

0.71s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

0.77s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

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

1.18s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core < 10 and active_visitor < 1000

1.15s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.11s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core > 90

1.03s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.06s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

Count Distinct

select count(distinct core), count(distinct db), count(distinct active_visitor) from AGG_TABLE

4.24s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

4.29s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, count(distinct db) count from AGG_TABLE group by CORE

1.8s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.95s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

IN/LIKE Clause

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

0.99s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

Load Data

LOAD DATA AGG_TABLE [2000000 ROWS]

107.53s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

124.83s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

Percentile

select percentile_cont(0.9) within group (order by active_visitor asc) from AGG_TABLE

3.09s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.13s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select percentile_cont(0.1) within group (order by active_visitor desc) from AGG_TABLE

3.09s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.08s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select percentile_cont(0.5) within group (order by core asc), percentile_cont(0.5) within group (order by db asc), percentile_cont(0.5) within group (order by active_visitor asc) from AGG_TABLE

4.27s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

4.35s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, percentile_cont(0.9999) within group (order by db asc) from AGG_TABLE group by CORE

1.85s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

2.01s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

Queries by Table

AGG_TABLE

Table used for various aggregation functions

ROWS 2000000 [2M]

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)) SPLIT ON ('CSGoogle','CSSalesforce','EUApple','EUGoogle','EUSalesforce','NAApple','NAGoogle','NASalesforce')

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 AGG_TABLE

0.5s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(1) from AGG_TABLE group by HOST

0.54s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, count(1) from AGG_TABLE group by CORE

0.66s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

0.74s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, host, sum(ACTIVE_VISITOR) from AGG_TABLE group by core, host

3.17s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select AVG(DB), MIN(CORE), MAX(ACTIVE_VISITOR) from AGG_TABLE where host='CS' and DOMAIN<>'Google.com'

3.81s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

4s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

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

1.19s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.22s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select trunc(date, 'hour', 1000), count(DB), count(ACTIVE_VISITOR) from AGG_TABLE where feature='Login' and (core<50 or db<20 or active_visitor > 200) group by trunc(date, 'hour', 1000)

1.9s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.94s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(active_visitor) count from AGG_TABLE

2.81s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

2.76s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(distinct active_visitor) count from AGG_TABLE

3.12s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.12s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select percentile_cont(0.9) within group (order by active_visitor asc) from AGG_TABLE

3.09s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.13s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select percentile_cont(0.1) within group (order by active_visitor desc) from AGG_TABLE

3.09s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.08s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(core), count(db), count(active_visitor) from AGG_TABLE

3.7s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

3.72s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(distinct core), count(distinct db), count(distinct active_visitor) from AGG_TABLE

4.24s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

4.29s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select percentile_cont(0.5) within group (order by core asc), percentile_cont(0.5) within group (order by db asc), percentile_cont(0.5) within group (order by active_visitor asc) from AGG_TABLE

4.27s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

4.35s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, count(db) count from AGG_TABLE group by CORE

0.71s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

0.77s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, count(distinct db) count from AGG_TABLE group by CORE

1.8s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.95s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select core, percentile_cont(0.9999) within group (order by db asc) from AGG_TABLE group by CORE

1.85s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

2.01s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

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

0.99s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

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

1.18s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core < 10 and active_visitor < 1000

1.15s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.11s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core > 90

1.03s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

1.06s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

LOAD DATA AGG_TABLE [2000000 ROWS]

107.53s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.14-SECURITY

124.83s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1

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