Apache Phoenix Performance Result :: Mon Apr 21 10:18:40 PDT 2014
Test Suite: AGGREGATION

Queries by Category

Aggregation

select count(*) from AGG_TABLE
0.54s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.51s 3.0-HBASE-0.94.15-SECURITY
0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.57s 4.0-HBASE-0.98.1-HADOOP1

select count(1) from AGG_TABLE group by HOST
0.57s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.56s 3.0-HBASE-0.94.15-SECURITY
0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.6s 4.0-HBASE-0.98.1-HADOOP1

select core, count(1) from AGG_TABLE group by CORE
0.68s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.73s 3.0-HBASE-0.94.15-SECURITY
0.74s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.77s 4.0-HBASE-0.98.1-HADOOP1

select core, host, sum(ACTIVE_VISITOR) from AGG_TABLE group by core, host
3.29s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.1s 3.0-HBASE-0.94.15-SECURITY
3.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.29s 4.0-HBASE-0.98.1-HADOOP1

select AVG(DB), MIN(CORE), MAX(ACTIVE_VISITOR) from AGG_TABLE where host='CS' and DOMAIN<>'Google.com'
3.94s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.95s 3.0-HBASE-0.94.15-SECURITY
4s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.13s 4.0-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.2s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.19s 3.0-HBASE-0.94.15-SECURITY
1.22s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.27s 4.0-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.91s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.84s 3.0-HBASE-0.94.15-SECURITY
1.94s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.96s 4.0-HBASE-0.98.1-HADOOP1

select count(active_visitor) count from AGG_TABLE
2.78s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
2.72s 3.0-HBASE-0.94.15-SECURITY
2.76s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.87s 4.0-HBASE-0.98.1-HADOOP1

select count(distinct active_visitor) count from AGG_TABLE
3.19s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.08s 3.0-HBASE-0.94.15-SECURITY
3.12s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.2s 4.0-HBASE-0.98.1-HADOOP1

select count(core), count(db), count(active_visitor) from AGG_TABLE
3.86s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.6s 3.0-HBASE-0.94.15-SECURITY
3.72s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.73s 4.0-HBASE-0.98.1-HADOOP1

select core, count(db) count from AGG_TABLE group by CORE
0.76s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.72s 3.0-HBASE-0.94.15-SECURITY
0.77s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.81s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core < 10 and db < 200
1.13s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.11s 3.0-HBASE-0.94.15-SECURITY
1.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.22s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core < 10 and active_visitor < 1000
1.13s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.08s 3.0-HBASE-0.94.15-SECURITY
1.11s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.14s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core > 90
1.04s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.98s 3.0-HBASE-0.94.15-SECURITY
1.06s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.09s 4.0-HBASE-0.98.1-HADOOP1

Count Distinct

select count(distinct core), count(distinct db), count(distinct active_visitor) from AGG_TABLE
4.46s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
4.18s 3.0-HBASE-0.94.15-SECURITY
4.29s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.44s 4.0-HBASE-0.98.1-HADOOP1

select core, count(distinct db) count from AGG_TABLE group by CORE
1.94s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.9s 3.0-HBASE-0.94.15-SECURITY
1.95s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.95s 4.0-HBASE-0.98.1-HADOOP1

IN/LIKE Clause

select count(core) from AGG_TABLE where core IN (1,25,50,75,100)
1s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.96s 3.0-HBASE-0.94.15-SECURITY
1s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.05s 4.0-HBASE-0.98.1-HADOOP1

Load Data

LOAD DATA AGG_TABLE [2000000 ROWS]
108.77s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
113.22s 3.0-HBASE-0.94.15-SECURITY
124.83s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
124.15s 4.0-HBASE-0.98.1-HADOOP1

Percentile

select percentile_cont(0.9) within group (order by active_visitor asc) from AGG_TABLE
3.16s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.02s 3.0-HBASE-0.94.15-SECURITY
3.13s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.14s 4.0-HBASE-0.98.1-HADOOP1

select percentile_cont(0.1) within group (order by active_visitor desc) from AGG_TABLE
3.18s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
2.94s 3.0-HBASE-0.94.15-SECURITY
3.08s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.1s 4.0-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.43s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
4.22s 3.0-HBASE-0.94.15-SECURITY
4.35s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.4s 4.0-HBASE-0.98.1-HADOOP1

select core, percentile_cont(0.9999) within group (order by db asc) from AGG_TABLE group by CORE
1.95s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.92s 3.0-HBASE-0.94.15-SECURITY
2.01s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.01s 4.0-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.54s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.51s 3.0-HBASE-0.94.15-SECURITY
0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.57s 4.0-HBASE-0.98.1-HADOOP1

select count(1) from AGG_TABLE group by HOST
0.57s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.56s 3.0-HBASE-0.94.15-SECURITY
0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.6s 4.0-HBASE-0.98.1-HADOOP1

select core, count(1) from AGG_TABLE group by CORE
0.68s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.73s 3.0-HBASE-0.94.15-SECURITY
0.74s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.77s 4.0-HBASE-0.98.1-HADOOP1

select core, host, sum(ACTIVE_VISITOR) from AGG_TABLE group by core, host
3.29s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.1s 3.0-HBASE-0.94.15-SECURITY
3.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.29s 4.0-HBASE-0.98.1-HADOOP1

select AVG(DB), MIN(CORE), MAX(ACTIVE_VISITOR) from AGG_TABLE where host='CS' and DOMAIN<>'Google.com'
3.94s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.95s 3.0-HBASE-0.94.15-SECURITY
4s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.13s 4.0-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.2s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.19s 3.0-HBASE-0.94.15-SECURITY
1.22s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.27s 4.0-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.91s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.84s 3.0-HBASE-0.94.15-SECURITY
1.94s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.96s 4.0-HBASE-0.98.1-HADOOP1

select count(active_visitor) count from AGG_TABLE
2.78s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
2.72s 3.0-HBASE-0.94.15-SECURITY
2.76s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.87s 4.0-HBASE-0.98.1-HADOOP1

select count(distinct active_visitor) count from AGG_TABLE
3.19s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.08s 3.0-HBASE-0.94.15-SECURITY
3.12s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.2s 4.0-HBASE-0.98.1-HADOOP1

select percentile_cont(0.9) within group (order by active_visitor asc) from AGG_TABLE
3.16s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.02s 3.0-HBASE-0.94.15-SECURITY
3.13s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.14s 4.0-HBASE-0.98.1-HADOOP1

select percentile_cont(0.1) within group (order by active_visitor desc) from AGG_TABLE
3.18s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
2.94s 3.0-HBASE-0.94.15-SECURITY
3.08s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.1s 4.0-HBASE-0.98.1-HADOOP1

select count(core), count(db), count(active_visitor) from AGG_TABLE
3.86s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
3.6s 3.0-HBASE-0.94.15-SECURITY
3.72s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.73s 4.0-HBASE-0.98.1-HADOOP1

select count(distinct core), count(distinct db), count(distinct active_visitor) from AGG_TABLE
4.46s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
4.18s 3.0-HBASE-0.94.15-SECURITY
4.29s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.44s 4.0-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.43s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
4.22s 3.0-HBASE-0.94.15-SECURITY
4.35s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.4s 4.0-HBASE-0.98.1-HADOOP1

select core, count(db) count from AGG_TABLE group by CORE
0.76s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.72s 3.0-HBASE-0.94.15-SECURITY
0.77s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.81s 4.0-HBASE-0.98.1-HADOOP1

select core, count(distinct db) count from AGG_TABLE group by CORE
1.94s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.9s 3.0-HBASE-0.94.15-SECURITY
1.95s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.95s 4.0-HBASE-0.98.1-HADOOP1

select core, percentile_cont(0.9999) within group (order by db asc) from AGG_TABLE group by CORE
1.95s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.92s 3.0-HBASE-0.94.15-SECURITY
2.01s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.01s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core IN (1,25,50,75,100)
1s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.96s 3.0-HBASE-0.94.15-SECURITY
1s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.05s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core < 10 and db < 200
1.13s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.11s 3.0-HBASE-0.94.15-SECURITY
1.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.22s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core < 10 and active_visitor < 1000
1.13s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
1.08s 3.0-HBASE-0.94.15-SECURITY
1.11s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.14s 4.0-HBASE-0.98.1-HADOOP1

select count(core) from AGG_TABLE where core > 90
1.04s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
0.98s 3.0-HBASE-0.94.15-SECURITY
1.06s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.09s 4.0-HBASE-0.98.1-HADOOP1

LOAD DATA AGG_TABLE [2000000 ROWS]
108.77s V3.0.0-INCUBATING-RELEASE-HBASE-0.94.15-SECURITY
113.22s 3.0-HBASE-0.94.15-SECURITY
124.83s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
124.15s 4.0-HBASE-0.98.1-HADOOP1


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