Apache Phoenix Performance Result :: Fri Apr 18 20:24:47 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.53s 3.0-HBASE-0.94.15-SECURITY
0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.74s 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.6s 3.0-HBASE-0.94.15-SECURITY
0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.62s 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.7s 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.25s 3.0-HBASE-0.94.15-SECURITY
3.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.49s 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
4.14s 3.0-HBASE-0.94.15-SECURITY
4s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.06s 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.2s 3.0-HBASE-0.94.15-SECURITY
1.22s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.24s 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.94s 3.0-HBASE-0.94.15-SECURITY
1.94s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.1s 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.76s 3.0-HBASE-0.94.15-SECURITY
2.76s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.93s 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.13s 3.0-HBASE-0.94.15-SECURITY
3.12s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.33s 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.82s 3.0-HBASE-0.94.15-SECURITY
3.72s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.02s 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.78s 3.0-HBASE-0.94.15-SECURITY
0.77s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.85s 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.18s 3.0-HBASE-0.94.15-SECURITY
1.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.27s 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.14s 3.0-HBASE-0.94.15-SECURITY
1.11s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.17s 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
1.01s 3.0-HBASE-0.94.15-SECURITY
1.06s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.1s 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.37s 3.0-HBASE-0.94.15-SECURITY
4.29s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.7s 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.95s 3.0-HBASE-0.94.15-SECURITY
1.95s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.01s 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
1.01s 3.0-HBASE-0.94.15-SECURITY
1s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.03s 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
109.39s 3.0-HBASE-0.94.15-SECURITY
124.83s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
123.47s 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.17s 3.0-HBASE-0.94.15-SECURITY
3.13s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.39s 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
3.14s 3.0-HBASE-0.94.15-SECURITY
3.08s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.38s 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.37s 3.0-HBASE-0.94.15-SECURITY
4.35s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.74s 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.98s 3.0-HBASE-0.94.15-SECURITY
2.01s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.08s 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.53s 3.0-HBASE-0.94.15-SECURITY
0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.74s 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.6s 3.0-HBASE-0.94.15-SECURITY
0.62s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.62s 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.7s 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.25s 3.0-HBASE-0.94.15-SECURITY
3.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.49s 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
4.14s 3.0-HBASE-0.94.15-SECURITY
4s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.06s 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.2s 3.0-HBASE-0.94.15-SECURITY
1.22s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.24s 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.94s 3.0-HBASE-0.94.15-SECURITY
1.94s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.1s 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.76s 3.0-HBASE-0.94.15-SECURITY
2.76s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.93s 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.13s 3.0-HBASE-0.94.15-SECURITY
3.12s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.33s 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.17s 3.0-HBASE-0.94.15-SECURITY
3.13s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.39s 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
3.14s 3.0-HBASE-0.94.15-SECURITY
3.08s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
3.38s 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.82s 3.0-HBASE-0.94.15-SECURITY
3.72s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.02s 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.37s 3.0-HBASE-0.94.15-SECURITY
4.29s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.7s 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.37s 3.0-HBASE-0.94.15-SECURITY
4.35s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
4.74s 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.78s 3.0-HBASE-0.94.15-SECURITY
0.77s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
0.85s 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.95s 3.0-HBASE-0.94.15-SECURITY
1.95s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.01s 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.98s 3.0-HBASE-0.94.15-SECURITY
2.01s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
2.08s 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
1.01s 3.0-HBASE-0.94.15-SECURITY
1s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.03s 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.18s 3.0-HBASE-0.94.15-SECURITY
1.19s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.27s 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.14s 3.0-HBASE-0.94.15-SECURITY
1.11s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.17s 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
1.01s 3.0-HBASE-0.94.15-SECURITY
1.06s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
1.1s 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
109.39s 3.0-HBASE-0.94.15-SECURITY
124.83s V4.0.0-INCUBATING-RELEASE-HBASE-0.98.1-HADOOP1
123.47s 4.0-HBASE-0.98.1-HADOOP1


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