G.3. ClickBench Benchmark Test #
G.3.1. Creating Analytical Tables for the ClickBench Benchmark Test #
Execute the following query:
COPY hits
TO '/home/test/clickbench/hits.parquet'
(FORMAT 'parquet', COMPRESSION 'zstd');
CREATE VIEW hits_parquet AS SELECT
r['watchid']::bigint AS watchid,
r['javaenable']::smallint AS javaenable,
r['title']::text AS title,
r['goodevent']::smallint AS goodevent,
r['eventtime']::timestamp AS eventtime,
r['eventdate']::date AS eventdate,
r['counterid']::integer AS counterid,
r['clientip']::integer AS clientip,
r['regionid']::integer AS regionid,
r['userid']::bigint AS userid,
r['counterclass']::smallint AS counterclass,
r['os']::smallint AS os,
r['useragent']::smallint AS useragent,
r['url']::text AS url,
r['referer']::text AS referer,
r['isrefresh']::smallint AS isrefresh,
r['referercategoryid']::smallint AS referercategoryid,
r['refererregionid']::integer AS refererregionid,
r['urlcategoryid']::smallint AS urlcategoryid,
r['urlregionid']::integer AS urlregionid,
r['resolutionwidth']::smallint AS resolutionwidth,
r['resolutionheight']::smallint AS resolutionheight,
r['resolutiondepth']::smallint AS resolutiondepth,
r['flashmajor']::smallint AS flashmajor,
r['flashminor']::smallint AS flashminor,
r['flashminor2']::text AS flashminor2,
r['netmajor']::smallint AS netmajor,
r['netminor']::smallint AS netminor,
r['useragentmajor']::smallint AS useragentmajor,
r['useragentminor']::text AS useragentminor,
r['cookieenable']::smallint AS cookieenable,
r['javascriptenable']::smallint AS javascriptenable,
r['ismobile']::smallint AS ismobile,
r['mobilephone']::smallint AS mobilephone,
r['mobilephonemodel']::text AS mobilephonemodel,
r['params']::text AS params,
r['ipnetworkid']::integer AS ipnetworkid,
r['traficsourceid']::smallint AS traficsourceid,
r['searchengineid']::smallint AS searchengineid,
r['searchphrase']::text AS searchphrase,
r['advengineid']::smallint AS advengineid,
r['isartifical']::smallint AS isartifical,
r['windowclientwidth']::smallint AS windowclientwidth,
r['windowclientheight']::smallint AS windowclientheight,
r['clienttimezone']::smallint AS clienttimezone,
r['clienteventtime']::timestamp AS clienteventtime,
r['silverlightversion1']::smallint AS silverlightversion1,
r['silverlightversion2']::smallint AS silverlightversion2,
r['silverlightversion3']::integer AS silverlightversion3,
r['silverlightversion4']::smallint AS silverlightversion4,
r['pagecharset']::text AS pagecharset,
r['codeversion']::integer AS codeversion,
r['islink']::smallint AS islink,
r['isdownload']::smallint AS isdownload,
r['isnotbounce']::smallint AS isnotbounce,
r['funiqid']::bigint AS funiqid,
r['originalurl']::text AS originalurl,
r['hid']::integer AS hid,
r['isoldcounter']::smallint AS isoldcounter,
r['isevent']::smallint AS isevent,
r['isparameter']::smallint AS isparameter,
r['dontcounthits']::smallint AS dontcounthits,
r['withhash']::smallint AS withhash,
r['hitcolor']::text AS hitcolor,
r['localeventtime']::timestamp AS localeventtime,
r['age']::smallint AS age,
r['sex']::smallint AS sex,
r['income']::smallint AS income,
r['interests']::smallint AS interests,
r['robotness']::smallint AS robotness,
r['remoteip']::integer AS remoteip,
r['windowname']::integer AS windowname,
r['openername']::integer AS openername,
r['historylength']::smallint AS historylength,
r['browserlanguage']::text AS browserlanguage,
r['browsercountry']::text AS browsercountry,
r['socialnetwork']::text AS socialnetwork,
r['socialaction']::text AS socialaction,
r['httperror']::smallint AS httperror,
r['sendtiming']::integer AS sendtiming,
r['dnstiming']::integer AS dnstiming,
r['connecttiming']::integer AS connecttiming,
r['responsestarttiming']::integer AS responsestarttiming,
r['responseendtiming']::integer AS responseendtiming,
r['fetchtiming']::integer AS fetchtiming,
r['socialsourcenetworkid']::smallint AS socialsourcenetworkid,
r['socialsourcepage']::text AS socialsourcepage,
r['paramprice']::bigint AS paramprice,
r['paramorderid']::text AS paramorderid,
r['paramcurrency']::text AS paramcurrency,
r['paramcurrencyid']::smallint AS paramcurrencyid,
r['openstatservicename']::text AS openstatservicename,
r['openstatcampaignid']::text AS openstatcampaignid,
r['openstatadid']::text AS openstatadid,
r['openstatsourceid']::text AS openstatsourceid,
r['utmsource']::text AS utmsource,
r['utmmedium']::text AS utmmedium,
r['utmcampaign']::text AS utmcampaign,
r['utmcontent']::text AS utmcontent,
r['utmterm']::text AS utmterm,
r['fromtag']::text AS fromtag,
r['hasgclid']::smallint AS hasgclid,
r['refererhash']::bigint AS refererhash,
r['urlhash']::bigint AS urlhash,
r['clid']::integer AS clid from
read_parquet('/home/test/clickbench/hits.parquet', binary_as_string => true) r;
G.3.2. ClickBench Benchmark Test: Scale Factor 100 #
Table G.10. Test Data
Data | Total size |
|---|---|
Analytical tables in the CSV format | 71.3 GB |
Analytical tables in the Parquet format | 9.3 GB |
Database | 66 GB |
Table G.11. Table Information
Analytical Table | Number of Rows |
|---|---|
| 99 997 497 |
Table G.12. Test Results
Query | Duration (s) |
|
|
|
|
|
|
|---|---|---|---|---|---|---|---|
Q1 | 0.63 | 0.45 | 0.07 | 125.90 | 77.85 | 31.49 | 0.00 |
Q1 — second execution | 0.47 | 0.39 | 0.04 | 73.11 | 71.41 | 0.00 | 0.00 |
Q2 | 0.79 | 0.56 | 0.11 | 158.62 | 89.55 | 36.81 | 0.00 |
Q2 — second execution | 0.53 | 0.36 | 0.07 | 135.46 | 92.99 | 0.00 | 0.00 |
Q3 | 0.91 | 1.23 | 0.20 | 167.48 | 85.73 | 131.00 | 0.00 |
Q3 — second execution | 0.60 | 0.98 | 0.10 | 148.55 | 110.61 | 0.00 | 0.00 |
Q4 | 1.00 | 0.81 | 0.27 | 220.86 | 112.01 | 284.62 | 0.00 |
Q4 — second execution | 0.59 | 0.92 | 0.13 | 136.31 | 101.65 | 0.00 | 0.00 |
Q5 | 1.20 | 3.14 | 0.70 | 708.39 | 216.61 | 382.22 | 0.00 |
Q5 — second execution | 0.92 | 2.86 | 0.48 | 620.24 | 209.80 | 0.00 | 0.00 |
Q6 | 1.50 | 4.54 | 0.93 | 1163.14 | 486.90 | 479.83 | 0.00 |
Q6 — second execution | 1.16 | 4.55 | 0.75 | 954.81 | 361.70 | 0.00 | 0.00 |
Q7 | 0.77 | 0.77 | 0.14 | 144.44 | 88.64 | 48.86 | 0.00 |
Q7 — second execution | 0.57 | 0.35 | 0.08 | 137.39 | 90.48 | 0.00 | 0.00 |
Q8 | 0.70 | 0.43 | 0.08 | 155.98 | 84.44 | 33.83 | 0.00 |
Q8 — second execution | 0.57 | 0.66 | 0.08 | 143.61 | 102.05 | 0.00 | 0.00 |
Q9 | 1.43 | 3.44 | 0.74 | 747.97 | 321.12 | 489.98 | 0.00 |
Q9 — second execution | 1.02 | 3.87 | 0.49 | 605.55 | 290.13 | 0.00 | 0.00 |
Q10 | 1.68 | 4.81 | 0.91 | 707.90 | 302.82 | 587.95 | 0.00 |
Q10 — second execution | 1.20 | 4.67 | 0.54 | 712.61 | 317.29 | 0.00 | 0.00 |
Q11 | 1.11 | 1.32 | 0.43 | 124.87 | 33.22 | 342.63 | 0.00 |
Q11 — second execution | 0.68 | 0.82 | 0.13 | 163.49 | 98.62 | 0.00 | 0.00 |
Q12 | 1.14 | 1.55 | 0.46 | 317.14 | 144.63 | 348.34 | 0.00 |
Q12 — second execution | 0.69 | 0.88 | 0.15 | 92.59 | 22.58 | 0.00 | 0.00 |
Q13 | 1.46 | 4.85 | 1.03 | 1260.84 | 523.72 | 482.57 | 0.00 |
Q13 — second execution | 1.18 | 4.69 | 0.78 | 1129.12 | 486.61 | 0.00 | 0.00 |
Q14 | 2.04 | 7.44 | 1.51 | 1312.48 | 615.04 | 828.38 | 0.00 |
Q14 — second execution | 1.52 | 6.58 | 1.04 | 1338.21 | 582.66 | 0.00 | 0.00 |
Q15 | 1.62 | 5.31 | 1.05 | 1262.77 | 530.42 | 494.15 | 0.00 |
Q15 — second execution | 1.21 | 4.76 | 0.79 | 1167.94 | 527.60 | 0.00 | 0.00 |
Q16 | 1.26 | 2.82 | 0.78 | 745.76 | 198.17 | 384.04 | 0.00 |
Q16 — second execution | 0.95 | 2.69 | 0.54 | 770.43 | 240.03 | 0.00 | 0.00 |
Q17 | 2.14 | 7.74 | 1.90 | 2271.80 | 988.01 | 829.14 | 0.00 |
Q17 — second execution | 1.70 | 8.43 | 1.36 | 2292.72 | 1122.14 | 0.00 | 0.00 |
Q18 | 2.13 | 7.77 | 1.79 | 2191.19 | 940.75 | 831.77 | 0.00 |
Q18 — second execution | 1.71 | 8.45 | 1.44 | 2184.30 | 1042.97 | 0.00 | 0.00 |
Q19 | 3.17 | 14.12 | 3.17 | 3353.14 | 1553.31 | 1349.53 | 0.00 |
Q19 — second execution | 2.54 | 13.22 | 2.43 | 3812.80 | 1997.06 | 0.00 | 0.00 |
Q20 | 0.88 | 0.92 | 0.34 | 132.50 | 71.91 | 350.26 | 0.00 |
Q20 — second execution | 0.58 | 0.86 | 0.11 | 78.63 | 41.52 | 0.00 | 0.00 |
Q21 | 5.71 | 35.14 | 1.80 | 528.65 | 295.37 | 2665.79 | 0.00 |
Q21 — second execution | 4.96 | 34.58 | 0.84 | 765.54 | 507.69 | 0.00 | 0.00 |
Q22 | 4.12 | 17.66 | 2.49 | 1280.60 | 787.99 | 3064.32 | 0.00 |
Q22 — second execution | 2.77 | 16.86 | 1.14 | 1043.66 | 667.71 | 0.00 | 0.00 |
Q23 | 5.65 | 25.08 | 3.60 | 1052.83 | 760.07 | 4476.53 | 0.00 |
Q23 — second execution | 3.73 | 23.60 | 1.80 | 806.15 | 525.07 | 61.22 | 0.00 |
Q24 | 2.27 | 10.19 | 0.70 | 353.58 | 224.77 | 1040.53 | 0.00 |
Q24 — second execution | 1.90 | 10.03 | 0.32 | 253.96 | 177.31 | 0.00 | 0.00 |
Q25 | 0.79 | 0.88 | 0.23 | 200.04 | 83.95 | 151.16 | 0.00 |
Q25 — second execution | 0.65 | 0.63 | 0.12 | 176.91 | 100.47 | 0.43 | 0.00 |
Q26 | 1.17 | 2.49 | 0.38 | 220.63 | 108.22 | 388.89 | 0.00 |
Q26 — second execution | 0.86 | 2.96 | 0.17 | 220.31 | 128.92 | 0.00 | 0.00 |
Q27 | 0.89 | 0.89 | 0.22 | 174.16 | 53.80 | 164.66 | 0.00 |
Q27 — second execution | 0.65 | 0.93 | 0.11 | 140.21 | 91.47 | 0.00 | 0.00 |
Q28 | 3.66 | 13.22 | 2.22 | 1306.91 | 794.35 | 2635.39 | 0.00 |
Q28 — second execution | 2.24 | 12.38 | 1.06 | 1003.94 | 573.61 | 0.00 | 0.00 |
Q29 | 12.23 | 82.03 | 3.60 | 1164.64 | 638.60 | 2695.30 | 0.00 |
Q29 — second execution | 11.47 | 81.52 | 2.62 | 1625.54 | 1002.23 | 0.00 | 0.00 |
Q30 | 0.78 | 0.83 | 0.20 | 149.80 | 88.79 | 116.46 | 0.00 |
Q30 — second execution | 0.58 | 0.37 | 0.06 | 51.00 | 18.07 | 0.00 | 0.00 |
Q31 | 2.03 | 5.78 | 1.22 | 701.85 | 369.72 | 1021.30 | 0.00 |
Q31 — second execution | 1.26 | 4.69 | 0.53 | 603.29 | 274.75 | 0.00 | 0.00 |
Q32 | 3.33 | 5.87 | 2.15 | 757.51 | 366.02 | 2516.53 | 0.00 |
Q32 — second execution | 1.25 | 4.67 | 0.79 | 895.50 | 376.31 | 0.00 | 0.00 |
Q33 | 4.08 | 12.12 | 4.79 | 5902.10 | 3058.48 | 2069.20 | 0.00 |
Q33 — second execution | 2.58 | 12.02 | 4.27 | 5291.00 | 2890.68 | 0.00 | 0.00 |
Q34 | 4.17 | 17.30 | 5.64 | 5521.45 | 2674.25 | 2648.54 | 0.00 |
Q34 — second execution | 3.19 | 16.57 | 4.18 | 5923.27 | 2989.65 | 0.00 | 0.00 |
Q35 | 4.36 | 17.58 | 6.10 | 5529.96 | 2811.14 | 2648.79 | 0.00 |
Q35 — second execution | 3.30 | 16.83 | 4.73 | 5503.18 | 2698.39 | 0.00 | 0.00 |
Q36 | 1.15 | 3.17 | 0.79 | 352.95 | 99.13 | 221.50 | 0.00 |
Q36 — second execution | 1.07 | 3.51 | 0.67 | 838.83 | 285.11 | 0.00 | 0.00 |
Q37 | 0.90 | 0.98 | 0.52 | 353.64 | 116.84 | 208.02 | 0.00 |
Q37 — second execution | 0.64 | 0.65 | 0.26 | 186.30 | 63.08 | 0.00 | 0.00 |
Q38 | 0.89 | 0.91 | 0.41 | 118.58 | 6.67 | 154.77 | 0.00 |
Q38 — second execution | 0.61 | 0.71 | 0.23 | 119.66 | 2.78 | 0.00 | 0.00 |
Q39 | 0.87 | 0.65 | 0.25 | 265.94 | 42.13 | 103.84 | 0.00 |
Q39 — second execution | 0.62 | 0.66 | 0.23 | 245.41 | 88.15 | 0.02 | 0.00 |
Q40 | 1.01 | 1.04 | 0.56 | 394.09 | 110.50 | 210.13 | 0.00 |
Q40 — second execution | 0.74 | 1.33 | 0.61 | 793.19 | 283.12 | 0.00 | 0.00 |
Q41 | 0.76 | 0.59 | 0.21 | 203.92 | 89.32 | 107.98 | 0.00 |
Q41 — second execution | 0.57 | 0.36 | 0.07 | 117.24 | 81.53 | 0.00 | 0.00 |
Q42 | 0.82 | 0.58 | 0.18 | 136.13 | 61.56 | 72.99 | 0.00 |
Q42 — second execution | 0.58 | 0.60 | 0.12 | 99.95 | 55.43 | 0.00 | 0.00 |
Q43 | 0.88 | 0.64 | 0.18 | 243.94 | 110.67 | 80.21 | 0.00 |
Q43 — second execution | 0.63 | 0.47 | 0.08 | 135.68 | 97.21 | 0.00 | 0.00 |