Phoenix Performance Result :: Sat Aug 24 15:21:00 PDT 2013

---

IN_LIKE_TABLE
Phoenix 1.2+ uses skip scan that gives significant performance improvement for IN/LIKE queries.

ROWS 4000000 [4M]

DDL CREATE TABLE IF NOT EXISTS $TABLE (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CF.A INTEGER, CF.B VARCHAR, CF.C VARCHAR CONSTRAINT PK PRIMARY KEY (K1,K2)) SPLIT ON ('B','C','D','E','F','G','H','I')

DATA GENERATOR
STRING :: Values: A|B|C|D|E|F|G|H|I
STRING :: Length: 10 Values: SEQUENTIAL
INTEGER :: Max. Value: 1000000 Values: RANDOM
STRING :: Length: 50 Values: RANDOM
STRING :: Length: 50 Values: RANDOM

QUERIES

select count(1) from IN_LIKE_TABLE where K1 in ('Z', 'D', 'F', 'X') 1.29s CURRENT-SNAPSHOT 1.22s PHOENIX-1.1 1.08s PHOENIX-1.2.0

select * from IN_LIKE_TABLE where K1 in ('A','B') and K2 in ('xxx1000000','xxx2000000','xxx2000001') 0.01s CURRENT-SNAPSHOT 1.78s PHOENIX-1.1 0.01s PHOENIX-1.2.0

select * from IN_LIKE_TABLE where K1 in ('A', 'C', 'E', 'F', 'H') and K2 like 'xxxxxx1%' 0.01s CURRENT-SNAPSHOT 7.7s PHOENIX-1.1 0.02s PHOENIX-1.2.0

select count(*) from IN_LIKE_TABLE where K1 in ('X','A','B','C','D','E','F','G','H','I','Z') and K2 like '%1%' 1.8s CURRENT-SNAPSHOT 1.61s PHOENIX-1.1 1.82s PHOENIX-1.2.0

select count(*) from IN_LIKE_TABLE where (K1 in ('A','E','F','G','H','I','Z') or K1 like 'E%') and (K2 like '%xx1%' or K2 like '%xxxxxx2%') 1.75s CURRENT-SNAPSHOT 1.66s PHOENIX-1.1 1.73s PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 102.43s CURRENT-SNAPSHOT 96.13s PHOENIX-1.1 124.22s PHOENIX-1.2.0

---

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

ROWS 4000000 [4M]

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)) 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);CREATE INDEX ids1 ON $TABLE (CORE) SALT_BUCKETS=16;CREATE INDEX ids2 ON $TABLE (DB) SALT_BUCKETS=16;CREATE INDEX ids3 ON $TABLE (DB,ACTIVE_VISITOR) SALT_BUCKETS=16;CREATE INDEX ids4 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

QUERIES

select count(*) from INDEXED_TEST_TABLE 0.93s CURRENT-SNAPSHOT 1.94s PHOENIX-1.1 2.73s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(*) from INDEXED_TEST_TABLE 4.54s CURRENT-SNAPSHOT 1.87s PHOENIX-1.1 2.74s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(*) from INDEXED_TEST_TABLE 0.86s CURRENT-SNAPSHOT 1.9s PHOENIX-1.1 2.51s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(*) from INDEXED_TEST_TABLE 1.34s CURRENT-SNAPSHOT 1.87s PHOENIX-1.1 2.56s PHOENIX-1.2.0

select count(1) from INDEXED_TEST_TABLE group by HOST 1.44s CURRENT-SNAPSHOT 2s PHOENIX-1.1 2.55s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(1) from INDEXED_TEST_TABLE group by HOST 1.4s CURRENT-SNAPSHOT 1.95s PHOENIX-1.1 2.65s PHOENIX-1.2.0

select MIN(CORE) from INDEXED_TEST_TABLE group by DOMAIN 1.03s CURRENT-SNAPSHOT 2.24s PHOENIX-1.1 3.02s PHOENIX-1.2.0

select /*+NO_INDEX*/ MIN(CORE) from INDEXED_TEST_TABLE group by DOMAIN 1.72s CURRENT-SNAPSHOT 2.2s PHOENIX-1.1 2.29s PHOENIX-1.2.0

select core, count(1) from INDEXED_TEST_TABLE group by CORE 5.2s CURRENT-SNAPSHOT 2.32s PHOENIX-1.1 2.18s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ core, count(1) from INDEXED_TEST_TABLE group by CORE 5.1s CURRENT-SNAPSHOT 2.27s PHOENIX-1.1 2.14s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ core, count(1) from INDEXED_TEST_TABLE group by CORE 0.99s CURRENT-SNAPSHOT 2.24s PHOENIX-1.1 2.13s PHOENIX-1.2.0

select /*+NO_INDEX*/ core, count(1) from INDEXED_TEST_TABLE group by CORE 1.64s CURRENT-SNAPSHOT 2.26s PHOENIX-1.1 2.14s PHOENIX-1.2.0

select core, host, sum(ACTIVE_VISITOR) from INDEXED_TEST_TABLE group by core, host 1.09s CURRENT-SNAPSHOT 3.57s PHOENIX-1.1 3.59s PHOENIX-1.2.0

select /*+NO_INDEX*/ core, host, sum(ACTIVE_VISITOR) from INDEXED_TEST_TABLE group by core, host 2.64s CURRENT-SNAPSHOT 3.54s PHOENIX-1.1 3.54s PHOENIX-1.2.0

select AVG(DB), MIN(CORE), MAX(ACTIVE_VISITOR) from INDEXED_TEST_TABLE where host='CS' and DOMAIN<>'Google.com' 3.51s CURRENT-SNAPSHOT 2.98s PHOENIX-1.1 3.24s PHOENIX-1.2.0

select /*+NO_INDEX*/ AVG(DB), MIN(CORE), MAX(ACTIVE_VISITOR) from INDEXED_TEST_TABLE where host='CS' and DOMAIN<>'Google.com' 3.52s CURRENT-SNAPSHOT 3.05s PHOENIX-1.1 3.23s PHOENIX-1.2.0

select round(date, 'day',50), sum(core)+2 from INDEXED_TEST_TABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host 0.23s CURRENT-SNAPSHOT 1.5s PHOENIX-1.1 1.49s PHOENIX-1.2.0

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TEST_TABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host 1.14s CURRENT-SNAPSHOT 1.5s PHOENIX-1.1 1.49s PHOENIX-1.2.0

select trunc(date, 'hour', 1000), count(DB), count(ACTIVE_VISITOR) from INDEXED_TEST_TABLE where feature='Login' and (core<50 or db<20 or active_visitor > 200) group by trunc(date, 'hour', 1000) 0.98s CURRENT-SNAPSHOT 2.78s PHOENIX-1.1 2.1s PHOENIX-1.2.0

select /*+NO_INDEX*/ trunc(date, 'hour', 1000), count(DB), count(ACTIVE_VISITOR) from INDEXED_TEST_TABLE where feature='Login' and (core<50 or db<20 or active_visitor > 200) group by trunc(date, 'hour', 1000) 1.45s CURRENT-SNAPSHOT 2.75s PHOENIX-1.1 2.1s PHOENIX-1.2.0

select count(active_visitor) count from INDEXED_TEST_TABLE 0.86s CURRENT-SNAPSHOT 3.02s PHOENIX-1.1 3.05s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(active_visitor) count from INDEXED_TEST_TABLE 2.26s CURRENT-SNAPSHOT 3.01s PHOENIX-1.1 3.08s PHOENIX-1.2.0

select count(distinct active_visitor) count from INDEXED_TEST_TABLE 0.85s CURRENT-SNAPSHOT N/A for PHOENIX-1.1 N/A for PHOENIX-1.2.0

select /*+NO_INDEX*/ count(distinct active_visitor) count from INDEXED_TEST_TABLE 2.64s CURRENT-SNAPSHOT N/A for PHOENIX-1.1 N/A for PHOENIX-1.2.0

select percentile_cont(0.9) within group (order by active_visitor asc) from INDEXED_TEST_TABLE 1.07s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select /*+NO_INDEX*/ percentile_cont(0.9) within group (order by active_visitor asc) from INDEXED_TEST_TABLE 2.61s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select percentile_cont(0.1) within group (order by active_visitor desc) from INDEXED_TEST_TABLE 1.01s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select /*+NO_INDEX*/ percentile_cont(0.1) within group (order by active_visitor desc) from INDEXED_TEST_TABLE 2.58s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select count(core), count(db), count(active_visitor) from INDEXED_TEST_TABLE 0.89s CURRENT-SNAPSHOT 4.23s PHOENIX-1.1 4.15s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(core), count(db), count(active_visitor) from INDEXED_TEST_TABLE 3.05s CURRENT-SNAPSHOT 4.2s PHOENIX-1.1 4.2s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(core), count(db), count(active_visitor) from INDEXED_TEST_TABLE 0.9s CURRENT-SNAPSHOT 4.28s PHOENIX-1.1 4.19s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core), count(db), count(active_visitor) from INDEXED_TEST_TABLE 3.16s CURRENT-SNAPSHOT 4.26s PHOENIX-1.1 4.19s PHOENIX-1.2.0

select count(distinct core), count(distinct db), count(distinct active_visitor) from INDEXED_TEST_TABLE 0.88s CURRENT-SNAPSHOT N/A for PHOENIX-1.1 N/A for PHOENIX-1.2.0

select /*+NO_INDEX*/ count(distinct core), count(distinct db), count(distinct active_visitor) from INDEXED_TEST_TABLE 3.76s CURRENT-SNAPSHOT N/A for PHOENIX-1.1 N/A for PHOENIX-1.2.0

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 INDEXED_TEST_TABLE 1.18s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select /*+NO_INDEX*/ 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 INDEXED_TEST_TABLE 3.82s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select core, count(db) count from INDEXED_TEST_TABLE group by CORE 3.24s CURRENT-SNAPSHOT 3.33s PHOENIX-1.1 3.07s PHOENIX-1.2.0

select /*+NO_INDEX*/ core, count(db) count from INDEXED_TEST_TABLE group by CORE 2.4s CURRENT-SNAPSHOT 3.31s PHOENIX-1.1 3.1s PHOENIX-1.2.0

select core, count(distinct db) count from INDEXED_TEST_TABLE group by CORE 3.22s CURRENT-SNAPSHOT N/A for PHOENIX-1.1 N/A for PHOENIX-1.2.0

select /*+NO_INDEX*/ core, count(distinct db) count from INDEXED_TEST_TABLE group by CORE 3.74s CURRENT-SNAPSHOT N/A for PHOENIX-1.1 N/A for PHOENIX-1.2.0

select core, percentile_cont(0.9999) within group (order by db asc) from INDEXED_TEST_TABLE group by CORE 3.45s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select /*+NO_INDEX*/ core, percentile_cont(0.9999) within group (order by db asc) from INDEXED_TEST_TABLE group by CORE 3.78s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core IN (1,100) 0.09s CURRENT-SNAPSHOT 1.1s PHOENIX-1.1 1.1s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TEST_TABLE where core IN (1,100) 0.13s CURRENT-SNAPSHOT 1.1s PHOENIX-1.1 1.11s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TEST_TABLE where core IN (1,100) 0.07s CURRENT-SNAPSHOT 1.1s PHOENIX-1.1 1.09s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core IN (1,100) 0.86s CURRENT-SNAPSHOT 1.09s PHOENIX-1.1 1.11s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core IN (1,25,50,75,100) 0.14s CURRENT-SNAPSHOT 1.15s PHOENIX-1.1 1.16s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TEST_TABLE where core IN (1,25,50,75,100) 0.23s CURRENT-SNAPSHOT 1.15s PHOENIX-1.1 1.14s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TEST_TABLE where core IN (1,25,50,75,100) 0.12s CURRENT-SNAPSHOT 1.15s PHOENIX-1.1 1.14s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core IN (1,25,50,75,100) 0.85s CURRENT-SNAPSHOT 1.16s PHOENIX-1.1 1.14s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core < 10 and db < 200 0.19s CURRENT-SNAPSHOT 1.42s PHOENIX-1.1 1.42s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TEST_TABLE where core < 10 and db < 200 1.16s CURRENT-SNAPSHOT 1.41s PHOENIX-1.1 1.44s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TEST_TABLE where core < 10 and db < 200 0.19s CURRENT-SNAPSHOT 1.44s PHOENIX-1.1 1.44s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core < 10 and db < 200 1.08s CURRENT-SNAPSHOT 1.42s PHOENIX-1.1 1.43s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core < 100 and db < 2000 1.19s CURRENT-SNAPSHOT 3.62s PHOENIX-1.1 3.5s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core < 100 and db < 2000 2.77s CURRENT-SNAPSHOT 3.39s PHOENIX-1.1 3.54s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core < 10 and active_visitor < 1000 0.18s CURRENT-SNAPSHOT 2.2s PHOENIX-1.1 1.21s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core < 10 and active_visitor < 1000 0.94s CURRENT-SNAPSHOT 2.14s PHOENIX-1.1 1.19s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core < 100 and active_visitor < 10000 1.14s CURRENT-SNAPSHOT 3.64s PHOENIX-1.1 1.27s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TEST_TABLE where core < 100 and active_visitor < 10000 3.37s CURRENT-SNAPSHOT 3.62s PHOENIX-1.1 1.29s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TEST_TABLE where core < 100 and active_visitor < 10000 1.11s CURRENT-SNAPSHOT 3.65s PHOENIX-1.1 1.31s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core < 100 and active_visitor < 10000 1s CURRENT-SNAPSHOT 3.63s PHOENIX-1.1 1.25s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core > 90 0.2s CURRENT-SNAPSHOT 1.2s PHOENIX-1.1 1.24s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TEST_TABLE where core > 90 0.92s CURRENT-SNAPSHOT 1.19s PHOENIX-1.1 1.25s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TEST_TABLE where core > 90 0.2s CURRENT-SNAPSHOT 1.2s PHOENIX-1.1 1.24s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core > 90 0.93s CURRENT-SNAPSHOT 1.18s PHOENIX-1.1 1.26s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core > 0 1.11s CURRENT-SNAPSHOT 2.28s PHOENIX-1.1 2.29s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TEST_TABLE where core > 0 5.01s CURRENT-SNAPSHOT 2.26s PHOENIX-1.1 2.19s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TEST_TABLE where core > 0 1.1s CURRENT-SNAPSHOT 2.28s PHOENIX-1.1 2.18s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core > 0 1.84s CURRENT-SNAPSHOT 2.25s PHOENIX-1.1 2.2s PHOENIX-1.2.0

select db from INDEXED_TEST_TABLE where db >5 and db <10 order by db 0.11s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select /*+NO_INDEX*/ db from INDEXED_TEST_TABLE where db >5 and db <10 order by db 1.7s CURRENT-SNAPSHOT SQL Exception - Check logs for PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select db from INDEXED_TEST_TABLE order by db limit 100 0.02s CURRENT-SNAPSHOT 0.08s PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

select /*+NO_INDEX*/ db from INDEXED_TEST_TABLE order by db limit 100 2.41s CURRENT-SNAPSHOT 0.08s PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 881.02s CURRENT-SNAPSHOT 174.46s PHOENIX-1.1 214.74s PHOENIX-1.2.0

---

CF_TABLE_14CF
Data is split in 14 column families. Phoenix 1.2+ uses Essential Column Family filter feature which fetches only required columns when filtering rows.

ROWS 4000000 [4M]

DDL CREATE TABLE IF NOT EXISTS $TABLE (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CF1.A INTEGER, CF2.B INTEGER, CF3.C INTEGER, CF4.D INTEGER, CF5.E INTEGER, CF6.F INTEGER, CF7.G INTEGER, CF8.H INTEGER, CF9.I INTEGER, CF10.J INTEGER, CF11.K INTEGER, CF12.L INTEGER, CF13.M INTEGER, CF14.N INTEGER CONSTRAINT PK PRIMARY KEY (K1,K2)) SPLIT ON ('B','C','D');CREATE TABLE IF NOT EXISTS $TABLE_UPSERTSELECT (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CF1.A INTEGER, CF2.B INTEGER, CF3.C INTEGER, CF4.D INTEGER, CF5.E INTEGER, CF6.F INTEGER, CF7.G INTEGER, CF8.H INTEGER, CF9.I INTEGER, CF10.J INTEGER, CF11.K INTEGER, CF12.L INTEGER, CF13.M INTEGER, CF14.N INTEGER CONSTRAINT PK PRIMARY KEY (K1,K2)) SPLIT ON ('B','C','D')

DATA GENERATOR
STRING :: Values: A|B|C|D
STRING :: Length: 10 Values: SEQUENTIAL
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM

QUERIES

select count(1) from CF_TABLE_14CF where K1 in ('A', 'B', 'C', 'D') 3.32s CURRENT-SNAPSHOT 3.35s PHOENIX-1.1 3.11s PHOENIX-1.2.0

select count(1) from CF_TABLE_14CF where K1 in ('A', 'C') 3.27s CURRENT-SNAPSHOT 3.12s PHOENIX-1.1 3.13s PHOENIX-1.2.0

select count(*) from CF_TABLE_14CF where N < 1000 1.73s CURRENT-SNAPSHOT 5.03s PHOENIX-1.1 1.66s PHOENIX-1.2.0

select a,b,c,d,e,f,g,h,i,j,k,l,m,n from CF_TABLE_14CF where (B>1000 and B<2000) and (M>1000 and M<2000) 2.43s CURRENT-SNAPSHOT 124.66s PHOENIX-1.1 2.31s PHOENIX-1.2.0

select sum(a),sum(e),sum(j),sum(n) from CF_TABLE_14CF group by K1 10.58s CURRENT-SNAPSHOT 10.49s PHOENIX-1.1 10.11s PHOENIX-1.2.0

upsert into CF_TABLE_14CF_UPSERTSELECT select * from CF_TABLE_14CF 171.06s CURRENT-SNAPSHOT 2313.79s PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 204.2s CURRENT-SNAPSHOT 182.61s PHOENIX-1.1 250.23s PHOENIX-1.2.0

---

SALTED_TABLE
This is Phoenix 1.2+ feature. Data is split in 4 regions equal to number of region servers in test cluster by adding salted byte to each key.

ROWS 4000000 [4M]

DDL CREATE TABLE $TABLE (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CFA.A VARCHAR, CFA.B CHAR(100), CFA.C INTEGER, CFB.D INTEGER CONSTRAINT PK PRIMARY KEY (K1,K2)) SALT_BUCKETS=4

DATA GENERATOR
STRING :: Values: A|B|C|D
STRING :: Length: 10 Values: SEQUENTIAL
STRING :: Length: 100 Values: RANDOM
STRING :: Length: 100 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM

QUERIES

select count(1) from SALTED_TABLE where K1 in ('A', 'B', 'C', 'D') 1.81s CURRENT-SNAPSHOT 7.46s PHOENIX-1.1 1.8s PHOENIX-1.2.0

select count(*) from SALTED_TABLE where D < 1000 0.91s CURRENT-SNAPSHOT 6.29s PHOENIX-1.1 0.89s PHOENIX-1.2.0

select avg(c),avg(d) from SALTED_TABLE group by K1 3.96s CURRENT-SNAPSHOT 10s PHOENIX-1.1 4.01s PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 129.91s CURRENT-SNAPSHOT 174.48s PHOENIX-1.1 140.5s PHOENIX-1.2.0

---

CF_TABLE_1CF
Same data as in 14 column family table but this one only has a single column family. Performance should be worse in most cases.

ROWS 4000000 [4M]

DDL CREATE TABLE IF NOT EXISTS $TABLE (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CF.A INTEGER, CF.B INTEGER, CF.C INTEGER, CF.D INTEGER, CF.E INTEGER, CF.F INTEGER, CF.G INTEGER, CF.H INTEGER, CF.I INTEGER, CF.J INTEGER, CF.K INTEGER, CF.L INTEGER, CF.M INTEGER, CF.N INTEGER CONSTRAINT PK PRIMARY KEY (K1,K2)) SPLIT ON ('B','C','D');CREATE TABLE IF NOT EXISTS $TABLE_UPSERTSELECT (K1 CHAR(1) NOT NULL, K2 VARCHAR NOT NULL, CF.A INTEGER, CF.B INTEGER, CF.C INTEGER, CF.D INTEGER, CF.E INTEGER, CF.F INTEGER, CF.G INTEGER, CF.H INTEGER, CF.I INTEGER, CF.J INTEGER, CF.K INTEGER, CF.L INTEGER, CF.M INTEGER, CF.N INTEGER CONSTRAINT PK PRIMARY KEY (K1,K2)) SPLIT ON ('B','C','D')

DATA GENERATOR
STRING :: Values: A|B|C|D
STRING :: Length: 10 Values: SEQUENTIAL
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM
INTEGER :: Max. Value: 1000000 Values: RANDOM

QUERIES

select count(1) from CF_TABLE_1CF where K1 in ('A', 'B', 'C', 'D') 4.38s CURRENT-SNAPSHOT 4s PHOENIX-1.1 3.96s PHOENIX-1.2.0

select count(1) from CF_TABLE_1CF where K1 in ('A', 'C') 4.36s CURRENT-SNAPSHOT 3.97s PHOENIX-1.1 3.95s PHOENIX-1.2.0

select count(*) from CF_TABLE_1CF where N < 1000 5.59s CURRENT-SNAPSHOT 5.25s PHOENIX-1.1 5.26s PHOENIX-1.2.0

select a,b,c,d,e,f,g,h,i,j,k,l,m,n from CF_TABLE_1CF where B>1000 and B<2000 and M>1000 and M<2000 6.7s CURRENT-SNAPSHOT 23.86s PHOENIX-1.1 6.27s PHOENIX-1.2.0

select sum(a),sum(e),sum(j),sum(n) from CF_TABLE_1CF group by K1 12.55s CURRENT-SNAPSHOT 11.9s PHOENIX-1.1 11.47s PHOENIX-1.2.0

upsert into CF_TABLE_1CF_UPSERTSELECT select * from CF_TABLE_1CF 114.22s CURRENT-SNAPSHOT 1959.68s PHOENIX-1.1 0.22s PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 176.08s CURRENT-SNAPSHOT 153.3s PHOENIX-1.1 215.95s PHOENIX-1.2.0

---

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