Phoenix Performance Result :: Sun Aug 25 17:22:38 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.26s CURRENT-SNAPSHOT 1.34s LAST-SNAPSHOT 1.25s PHOENIX-1.1 1.11s PHOENIX-1.2.0

select * from IN_LIKE_TABLE where K1 in ('A','B') and K2 in ('xxx1000000','xxx2000000','xxx2000001') 0.01s CURRENT-SNAPSHOT 0.01s LAST-SNAPSHOT 1.77s 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.02s CURRENT-SNAPSHOT 0.01s LAST-SNAPSHOT 7.72s 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.81s CURRENT-SNAPSHOT 1.92s LAST-SNAPSHOT 1.62s PHOENIX-1.1 1.7s 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.86s CURRENT-SNAPSHOT 1.87s LAST-SNAPSHOT 1.64s PHOENIX-1.1 1.79s PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 100.88s CURRENT-SNAPSHOT 100.12s LAST-SNAPSHOT 97.99s PHOENIX-1.1 110.69s 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 1s CURRENT-SNAPSHOT 0.88s LAST-SNAPSHOT 2.17s PHOENIX-1.1 1.98s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(*) from INDEXED_TEST_TABLE 2.16s CURRENT-SNAPSHOT 3.82s LAST-SNAPSHOT 2.09s PHOENIX-1.1 1.86s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(*) from INDEXED_TEST_TABLE 0.86s CURRENT-SNAPSHOT 0.82s LAST-SNAPSHOT 2.02s PHOENIX-1.1 1.86s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(*) from INDEXED_TEST_TABLE 1.4s CURRENT-SNAPSHOT 1.37s LAST-SNAPSHOT 2.02s PHOENIX-1.1 1.87s PHOENIX-1.2.0

select count(1) from INDEXED_TEST_TABLE group by HOST 1.43s CURRENT-SNAPSHOT 1.47s LAST-SNAPSHOT 2.11s PHOENIX-1.1 1.94s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(1) from INDEXED_TEST_TABLE group by HOST 1.4s CURRENT-SNAPSHOT 1.41s LAST-SNAPSHOT 2.13s PHOENIX-1.1 1.91s PHOENIX-1.2.0

select MIN(CORE) from INDEXED_TEST_TABLE group by DOMAIN 1.03s CURRENT-SNAPSHOT 1.02s LAST-SNAPSHOT 2.45s PHOENIX-1.1 2.34s PHOENIX-1.2.0

select /*+NO_INDEX*/ MIN(CORE) from INDEXED_TEST_TABLE group by DOMAIN 1.76s CURRENT-SNAPSHOT 1.8s LAST-SNAPSHOT 2.39s PHOENIX-1.1 2.28s PHOENIX-1.2.0

select core, count(1) from INDEXED_TEST_TABLE group by CORE 2.52s CURRENT-SNAPSHOT 4.14s LAST-SNAPSHOT 2.43s PHOENIX-1.1 2.24s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ core, count(1) from INDEXED_TEST_TABLE group by CORE 2.53s CURRENT-SNAPSHOT 4.2s LAST-SNAPSHOT 2.48s PHOENIX-1.1 2.19s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ core, count(1) from INDEXED_TEST_TABLE group by CORE 0.96s CURRENT-SNAPSHOT 0.98s LAST-SNAPSHOT 2.45s PHOENIX-1.1 2.25s PHOENIX-1.2.0

select /*+NO_INDEX*/ core, count(1) from INDEXED_TEST_TABLE group by CORE 1.65s CURRENT-SNAPSHOT 1.68s LAST-SNAPSHOT 2.46s PHOENIX-1.1 2.23s PHOENIX-1.2.0

select core, host, sum(ACTIVE_VISITOR) from INDEXED_TEST_TABLE group by core, host 1.08s CURRENT-SNAPSHOT 1.16s LAST-SNAPSHOT 3.64s PHOENIX-1.1 3.56s PHOENIX-1.2.0

select /*+NO_INDEX*/ core, host, sum(ACTIVE_VISITOR) from INDEXED_TEST_TABLE group by core, host 2.68s CURRENT-SNAPSHOT 2.78s LAST-SNAPSHOT 3.77s PHOENIX-1.1 3.3s PHOENIX-1.2.0

select AVG(DB), MIN(CORE), MAX(ACTIVE_VISITOR) from INDEXED_TEST_TABLE where host='CS' and DOMAIN<>'Google.com' 3.46s CURRENT-SNAPSHOT 3.4s LAST-SNAPSHOT 3.16s PHOENIX-1.1 3.2s 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.46s CURRENT-SNAPSHOT 3.39s LAST-SNAPSHOT 3.16s PHOENIX-1.1 3.12s 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.26s CURRENT-SNAPSHOT 0.24s LAST-SNAPSHOT 1.64s PHOENIX-1.1 1.43s 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.17s CURRENT-SNAPSHOT 1.15s LAST-SNAPSHOT 1.62s PHOENIX-1.1 1.41s 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) 1.08s CURRENT-SNAPSHOT 1s LAST-SNAPSHOT 3.01s PHOENIX-1.1 2.13s 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.55s CURRENT-SNAPSHOT 1.51s LAST-SNAPSHOT 3.05s PHOENIX-1.1 2.09s PHOENIX-1.2.0

select count(active_visitor) count from INDEXED_TEST_TABLE 0.88s CURRENT-SNAPSHOT 0.88s LAST-SNAPSHOT 3.14s PHOENIX-1.1 2.79s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(active_visitor) count from INDEXED_TEST_TABLE 2.34s CURRENT-SNAPSHOT 2.31s LAST-SNAPSHOT 3.19s PHOENIX-1.1 2.8s PHOENIX-1.2.0

select count(distinct active_visitor) count from INDEXED_TEST_TABLE 0.91s CURRENT-SNAPSHOT 0.82s LAST-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.77s CURRENT-SNAPSHOT 2.64s LAST-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.06s CURRENT-SNAPSHOT 1.08s LAST-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.79s CURRENT-SNAPSHOT 2.6s LAST-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.08s CURRENT-SNAPSHOT 1.06s LAST-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.78s CURRENT-SNAPSHOT 2.63s LAST-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.91s CURRENT-SNAPSHOT 0.89s LAST-SNAPSHOT 4.31s PHOENIX-1.1 3.88s 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.32s CURRENT-SNAPSHOT 1.94s LAST-SNAPSHOT 4.26s PHOENIX-1.1 3.86s 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.92s CURRENT-SNAPSHOT 0.9s LAST-SNAPSHOT 4.37s PHOENIX-1.1 3.9s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core), count(db), count(active_visitor) from INDEXED_TEST_TABLE 3.3s CURRENT-SNAPSHOT 3.18s LAST-SNAPSHOT 4.39s PHOENIX-1.1 3.85s PHOENIX-1.2.0

select count(distinct core), count(distinct db), count(distinct active_visitor) from INDEXED_TEST_TABLE 0.93s CURRENT-SNAPSHOT 0.94s LAST-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 4.09s CURRENT-SNAPSHOT 3.95s LAST-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.23s CURRENT-SNAPSHOT 1.27s LAST-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 4.13s CURRENT-SNAPSHOT 3.88s LAST-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.39s CURRENT-SNAPSHOT 2.02s LAST-SNAPSHOT 3.41s PHOENIX-1.1 2.88s PHOENIX-1.2.0

select /*+NO_INDEX*/ core, count(db) count from INDEXED_TEST_TABLE group by CORE 2.37s CURRENT-SNAPSHOT 2.39s LAST-SNAPSHOT 3.42s PHOENIX-1.1 2.89s PHOENIX-1.2.0

select core, count(distinct db) count from INDEXED_TEST_TABLE group by CORE 3.37s CURRENT-SNAPSHOT 1.99s LAST-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.9s CURRENT-SNAPSHOT 3.78s LAST-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.73s CURRENT-SNAPSHOT 2.23s LAST-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 4.05s CURRENT-SNAPSHOT 3.87s LAST-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 0.08s LAST-SNAPSHOT 1.16s PHOENIX-1.1 1.02s 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 0.11s LAST-SNAPSHOT 1.14s PHOENIX-1.1 1.01s 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.08s CURRENT-SNAPSHOT 0.08s LAST-SNAPSHOT 1.12s PHOENIX-1.1 1.02s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core IN (1,100) 0.94s CURRENT-SNAPSHOT 0.83s LAST-SNAPSHOT 1.2s PHOENIX-1.1 1.01s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core IN (1,25,50,75,100) 0.14s CURRENT-SNAPSHOT 0.15s LAST-SNAPSHOT 1.22s PHOENIX-1.1 1.05s 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.13s CURRENT-SNAPSHOT 0.2s LAST-SNAPSHOT 1.2s PHOENIX-1.1 1.06s 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.14s CURRENT-SNAPSHOT 0.13s LAST-SNAPSHOT 1.23s PHOENIX-1.1 1.06s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core IN (1,25,50,75,100) 0.94s CURRENT-SNAPSHOT 0.9s LAST-SNAPSHOT 1.22s PHOENIX-1.1 1.06s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core < 10 and db < 200 0.21s CURRENT-SNAPSHOT 0.2s LAST-SNAPSHOT 1.5s PHOENIX-1.1 1.39s 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.28s CURRENT-SNAPSHOT 0.57s LAST-SNAPSHOT 1.41s PHOENIX-1.1 1.4s 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.18s CURRENT-SNAPSHOT 0.18s LAST-SNAPSHOT 1.4s PHOENIX-1.1 1.4s PHOENIX-1.2.0

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

select count(core) from INDEXED_TEST_TABLE where core < 100 and db < 2000 1.19s CURRENT-SNAPSHOT 1.16s LAST-SNAPSHOT 3.58s PHOENIX-1.1 3.48s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core < 100 and db < 2000 2.82s CURRENT-SNAPSHOT 2.75s LAST-SNAPSHOT 3.6s PHOENIX-1.1 3.47s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core < 10 and active_visitor < 1000 0.16s CURRENT-SNAPSHOT 0.18s LAST-SNAPSHOT 2.29s PHOENIX-1.1 1.16s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core < 10 and active_visitor < 1000 0.95s CURRENT-SNAPSHOT 0.92s LAST-SNAPSHOT 2.31s PHOENIX-1.1 1.16s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core < 100 and active_visitor < 10000 1.2s CURRENT-SNAPSHOT 1.15s LAST-SNAPSHOT 3.82s PHOENIX-1.1 1.25s 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.57s CURRENT-SNAPSHOT 2.07s LAST-SNAPSHOT 3.82s PHOENIX-1.1 1.24s 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.17s CURRENT-SNAPSHOT 1.14s LAST-SNAPSHOT 3.8s PHOENIX-1.1 1.23s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core < 100 and active_visitor < 10000 1.06s CURRENT-SNAPSHOT 0.95s LAST-SNAPSHOT 3.81s PHOENIX-1.1 1.28s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core > 90 0.2s CURRENT-SNAPSHOT 0.19s LAST-SNAPSHOT 1.21s PHOENIX-1.1 1.18s PHOENIX-1.2.0

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

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

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core > 90 0.97s CURRENT-SNAPSHOT 0.93s LAST-SNAPSHOT 1.2s PHOENIX-1.1 1.17s PHOENIX-1.2.0

select count(core) from INDEXED_TEST_TABLE where core > 0 1.18s CURRENT-SNAPSHOT 1.16s LAST-SNAPSHOT 2.33s PHOENIX-1.1 2.22s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TEST_TABLE where core > 0 2.41s CURRENT-SNAPSHOT 4.17s LAST-SNAPSHOT 2.33s PHOENIX-1.1 2.22s PHOENIX-1.2.0

select /*+ INDEX(INDEXED_TEST_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TEST_TABLE where core > 0 1.21s CURRENT-SNAPSHOT 1.06s LAST-SNAPSHOT 2.34s PHOENIX-1.1 2.22s PHOENIX-1.2.0

select /*+NO_INDEX*/ count(core) from INDEXED_TEST_TABLE where core > 0 1.84s CURRENT-SNAPSHOT 1.81s LAST-SNAPSHOT 2.32s PHOENIX-1.1 2.22s PHOENIX-1.2.0

select db from INDEXED_TEST_TABLE where db >5 and db <10 order by db 0.11s CURRENT-SNAPSHOT 0.09s LAST-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.74s CURRENT-SNAPSHOT 1.66s LAST-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.03s CURRENT-SNAPSHOT 0.06s LAST-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.49s CURRENT-SNAPSHOT 2.42s LAST-SNAPSHOT 0.08s PHOENIX-1.1 SQL Exception - Check logs for PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 861.53s CURRENT-SNAPSHOT 913.62s LAST-SNAPSHOT 175.91s PHOENIX-1.1 186.86s 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.29s CURRENT-SNAPSHOT 3.33s LAST-SNAPSHOT 3.4s PHOENIX-1.1 3.11s PHOENIX-1.2.0

select count(1) from CF_TABLE_14CF where K1 in ('A', 'C') 3.26s CURRENT-SNAPSHOT 3.3s LAST-SNAPSHOT 3.23s PHOENIX-1.1 3.05s PHOENIX-1.2.0

select count(*) from CF_TABLE_14CF where N < 1000 1.81s CURRENT-SNAPSHOT 1.8s LAST-SNAPSHOT 4.79s PHOENIX-1.1 1.67s 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.48s CURRENT-SNAPSHOT 2.38s LAST-SNAPSHOT 125.99s PHOENIX-1.1 2.3s PHOENIX-1.2.0

select sum(a),sum(e),sum(j),sum(n) from CF_TABLE_14CF group by K1 10.65s CURRENT-SNAPSHOT 10.69s LAST-SNAPSHOT 10.12s PHOENIX-1.1 9.74s PHOENIX-1.2.0

upsert into CF_TABLE_14CF_UPSERTSELECT select * from CF_TABLE_14CF 176.08s CURRENT-SNAPSHOT 170.96s LAST-SNAPSHOT 2269.76s PHOENIX-1.1 0.23s PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 205.28s CURRENT-SNAPSHOT 205.2s LAST-SNAPSHOT 183.96s PHOENIX-1.1 246.75s 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.89s CURRENT-SNAPSHOT 1.85s LAST-SNAPSHOT 8.24s PHOENIX-1.1 1.64s PHOENIX-1.2.0

select count(*) from SALTED_TABLE where D < 1000 0.89s CURRENT-SNAPSHOT 0.93s LAST-SNAPSHOT 6.76s PHOENIX-1.1 0.85s PHOENIX-1.2.0

select avg(c),avg(d) from SALTED_TABLE group by K1 4.1s CURRENT-SNAPSHOT 4.01s LAST-SNAPSHOT 10.54s PHOENIX-1.1 3.84s PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 126.37s CURRENT-SNAPSHOT 130.66s LAST-SNAPSHOT 180.3s PHOENIX-1.1 136.81s 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.5s CURRENT-SNAPSHOT 4.44s LAST-SNAPSHOT 4.22s PHOENIX-1.1 3.82s PHOENIX-1.2.0

select count(1) from CF_TABLE_1CF where K1 in ('A', 'C') 4.38s CURRENT-SNAPSHOT 4.45s LAST-SNAPSHOT 4.22s PHOENIX-1.1 3.56s PHOENIX-1.2.0

select count(*) from CF_TABLE_1CF where N < 1000 5.53s CURRENT-SNAPSHOT 5.39s LAST-SNAPSHOT 5.26s PHOENIX-1.1 5.22s 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.36s CURRENT-SNAPSHOT 6.33s LAST-SNAPSHOT 24.24s PHOENIX-1.1 6.22s PHOENIX-1.2.0

select sum(a),sum(e),sum(j),sum(n) from CF_TABLE_1CF group by K1 12.01s CURRENT-SNAPSHOT 12.23s LAST-SNAPSHOT 11.87s PHOENIX-1.1 11.8s PHOENIX-1.2.0

upsert into CF_TABLE_1CF_UPSERTSELECT select * from CF_TABLE_1CF 108.61s CURRENT-SNAPSHOT 105.73s LAST-SNAPSHOT 1941.15s PHOENIX-1.1 0.2s PHOENIX-1.2.0

LOAD DATA [4000000 ROWS] 172.27s CURRENT-SNAPSHOT 172.38s LAST-SNAPSHOT 155.34s PHOENIX-1.1 208.53s PHOENIX-1.2.0

---

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