1<!DOCTYPE html> 2<html lang="en"> 3 4<head> 5 <link href='//fonts.googleapis.com/css?family=Roboto:900,400' rel='stylesheet' type='text/css'> 6 <link href='//fonts.googleapis.com/css?family=Droid+Sans+Mono' rel='stylesheet' type='text/css'> 7 <link rel="stylesheet" type="text/css" href="./theme/css/style.css"> 8 <link rel="stylesheet" type="text/css" href="./theme/css/syntax.css"> 9 <meta charset="utf-8" /> 10 <meta name="viewport" content="width=device-width; initial-scale=1.0"> 11 <meta name="author" content="Mark Litwintschik"> 12 <meta name="description" content="Benchmarks & Tips for Big Data, Hadoop, AWS, Google Cloud, PostgreSQL, Spark, Python & More..."> 13 14 <link rel="shortcut icon" href="./theme/images/mark.jpg" type="image/x-icon" /> 15 16 <link href="https://tech.marksblogg.com/feeds/all.atom.xml" type="application/atom+xml" rel="alternate" title="Tech Blog Full Atom Feed" /> 17 18 19 <title> 20Summary of the 1.1 Billion Taxi Rides Benchmarks </title> 21 22</head> 23 24<body> 25 <aside> 26 <div id="user_meta"> 27 <center> 28 <a href="."> 29 <img src="./theme/images/mark.jpg" alt="Mark"> 30 </a> 31 <h2><a href=".">Mark Litwintschik</a></h2> 32 </center> 33 <p>I'm a Big Data, AI, GIS & Networking Consultant with clients in the UK, USA, Sweden, Ireland & Germany. Past clients include BAA plc, Bank of America Merrill Lynch, Blackberry, Bloomberg, British Telecom, Ford, Google, ITV, IMG, Nando's, News UK, Pizza Hut, Royal Mail, T-Mobile, Williams Formula 1, Wise & UBS. I hold both a Canadian and a British passport as well as permanent residence in Estonia. Find me on <a href="https://uk.linkedin.com/in/marklitwintschik/">LinkedIn</a> & <a href="https://x.com/marklit82">X</a>. 34 </p> 35 <ul> 36 </ul> 37 </div> 38 </aside> 39 40 <main> 41 <header> 42 <p> 43 <a href=".">Home</a> 44 | <a href="./benchmarks.html">Benchmarks</a> 45 | <a href="./categories.html">Categories</a> 46 47 | <a href="https://tech.marksblogg.com/feeds/all.atom.xml">Atom Feed</a> 48 49 </p> 50 51 <p>Updated on Tue 19 March 2024</p> 52 </header> 53 54 <article> 55 <div id="article_title"> 56 <h3><a href="./benchmarks.html">Summary of the 1.1 Billion Taxi Rides Benchmarks</a></h3> 57 </div> 58 <div id="article_text"> 59 <p>This table lists the fastest query times (measured in seconds) seen in each of my benchmarks broken down by software and hardware setup.</p> 60<p>The dataset used has 1.1 billion records, 51 columns and is 500 GB in size when in uncompressed CSV format. Instructions on producing the dataset can be found in my <a class="reference external" href="billion-nyc-taxi-rides-redshift.html">Billion Taxi Rides in Redshift</a> blog post.</p> 61<div class="justtable"> 62 <table> 63 <thead> 64 <tr> 65 <th>Query 1</th> 66 <th>Query 2</th> 67 <th>Query 3</th> 68 <th>Query 4</th> 69 <th>Setup</th> 70 </tr> 71 </thead> 72 <tbody> 73 <tr> 74 <td>0.005</td> 75 <td>0.011</td> 76 <td>0.103</td> 77 <td>0.188</td> 78 <td> 79 <a class="reference external" href="billion-nyc-taxi-rides-brytlytdb-ibm-minsky.html"><strong>BrytlytDB</strong> 2.1 & a 5-node IBM Minsky cluster</a> 80 </td> 81 </tr> 82 <tr> 83 <td>0.009</td> 84 <td>0.027</td> 85 <td>0.287</td> 86 <td>0.428</td> 87 <td> 88 <a class="reference external" href="billion-nyc-taxi-rides-p2-16xlarge-brytlytdb-2.html"><strong>BrytlytDB</strong> 2.0 & a 2-node p2.16xlarge cluster</a> 89 </td> 90 </tr> 91 <tr> 92 <td>0.021</td> 93 <td>0.053</td> 94 <td>0.165</td> 95 <td>0.51</td> 96 <td> 97 <a class="reference external" href="billion-nyc-taxi-rides-nvidia-pascal-titan-x-mapd.html"><strong>OmniSci</strong> & 8 Nvidia Pascal Titan Xs</a> 98 </td> 99 </tr> 100 <tr> 101 <td>
1010.027</td> 102 <td>0.083</td> 103 <td>0.163</td> 104 <td>0.891</td> 105 <td> 106 <a class="reference external" href="billion-nyc-taxi-rides-nvidia-tesla-mapd.html"><strong>OmniSci</strong> & 8 Nvidia Tesla K80s</a> 107 </td> 108 </tr> 109 <tr> 110 <td>0.028</td> 111 <td>0.2</td> 112 <td>0.237</td> 113 <td>0.578</td> 114 <td> 115 <a class="reference external" href="billion-nyc-taxi-rides-aws-ec2-mapd.html"><strong>OmniSci</strong> & a 4-node g2.8xlarge cluster</a> 116 </td> 117 </tr> 118 <tr> 119 <td>0.034</td> 120 <td>0.061</td> 121 <td>0.178</td> 122 <td>0.498</td> 123 <td> 124 <a class="reference external" href="billion-nyc-taxi-rides-aws-ec2-p2-8xlarge-mapd.html"><strong>OmniSci</strong> & a 2-node p2.8xlarge cluster</a> 125 </td> 126 </tr> 127 <tr> 128 <td>0.036</td> 129 <td>0.131</td> 130 <td>0.439</td> 131 <td>0.964</td> 132 <td> 133 <a class="reference external" href="billion-nyc-taxi-rides-nvidia-titan-x-mapd.html"><strong>OmniSci</strong> & 4 Nvidia Titan Xs</a> 134 </td> 135 </tr> 136 <tr> 137 <td>0.051</td> 138 <td>0.146</td> 139 <td>0.047</td> 140 <td>0.794</td> 141 <td> 142 <a class="reference external" href="billion-nyc-taxi-kdb.html"><strong>kdb+/q</strong> & 4 Intel Xeon Phi 7210 CPUs</a> 143 </td> 144 </tr> 145 <tr> 146 <td>0.088</td> 147 <td>0.51</td> 148 <td>0.424</td> 149 <td>1.257</td> 150 <td> 151 <a class="reference external" href="clickhouse-14900k-1b-taxi-rides.html"><strong>ClickHouse</strong> & an Intel Core i9-14900K</a> 152 </td> 153 </tr> 154 <tr> 155 <td>0.134</td> 156 <td>0.349</td> 157 <td>0.542</td> 158 <td>3.312</td> 159 <td> 160 <a class="reference external" href="omnisci-macos-macbookpro-mbp.html"><strong>OmniSci</strong> & a 16" MacBook Pro</a> 161 </td> 162 </tr> 163 <tr> 164 <td>0.241</td> 165 <td>0.826</td> 166 <td>1.209</td> 167 <td>1.781</td> 168 <td> 169 <a class="reference external" href="billion-nyc-taxi-rides-clickhouse-cluster.html"><strong>ClickHouse</strong> & a 3 x c5d.9xlarge cluster</a> 170 </td> 171 </tr> 172 <tr> 173 <td>0.347</td> 174 <td>1.1</td> 175 <td>1.389</td> 176 <td>2.935</td> 177 <td> 178 <a class="reference external" href="billion-taxi-rides-doublecloud-clickhouse.html"><strong>Clickhouse</strong> on DoubleCloud, s1-c32-m128</a> 179 </td> 180 </tr> 181 <tr> 182 <td>0.466</td> 183 <td>1.094</td> 184 <td>0.742</td> 185 <td>1.412</td> 186 <td> 187 <a class="reference external" href="hydrolix-1b-taxi-rides-aws.html"><strong>Hydrolix</strong> & a c5n.9xlarge cluster</a> 188 </td> 189 </tr> 190 <tr> 191 <td>0.498</td> 192 <td>0.234</td> 193 <td>0.734</td> 194 <td>1.334</td> 195 <td> 196 <a class="reference external" href="duckdb-1b-taxi-rides.html"><strong>DuckDB</strong> 0.10.0 & an Intel Core i9-14900K</a> 197 </td> 198 </tr> 199 <tr> 200 <td>0.762</td> 201 <td>2.472</td> 202 <td>4.131</td> 203 <td>6.041</td> 204 <td> 205 <a class="reference external" href="billion-nyc-taxi-rides-aws-ec2-p2-16xlarge-brytlytdb.html"><strong>BrytlytDB</strong> 1.0 & a 2-node p2.16xlarge cluster</a> 206 </td> 207 </tr> 208 <tr> 209 <td>1.034</td> 210 <td>3.058</td> 211 <td>5.354</td> 212 <td>12.748</td> 213 <td> 214 <a class="reference external" href="billion-nyc-taxi-clickhouse.html"><strong>ClickHouse</strong> & an Intel Core i5 4670K</a> 215 </td> 216 </tr> 217 <tr> 218 <td>1.56</td> 219 <td>1.25</td> 220 <td>2.25</td> 221 <td>2.97</td> 222 <td> 223 <a class="reference external" href="billion-nyc-taxi-rides-redshift-large-cluster.html"><strong>Redshift</strong> & a 6-node ds2.8xlarge cluster</a> 224 </td> 225 </tr> 226 <tr> 227 <td>2</td> 228 <td>2</td> 229 <td>1</td> 230 <td>3</td> 231 <td> 232 <a class="reference external" href="billion-nyc-taxi-rides-bigquery.html"><strong>BigQuery</strong></a> 233 </td> 234 </tr> 235 <tr> 236 <td>2.362</td> 237 <td>3.559</td> 238 <td>4.019</td> 239 <td>20.412</td> 240 <td> 241 <a class="reference external" href="billion-nyc-taxi-rides-spark-2-4-versus-presto-214.html"><strong>Spark</strong> 2.4 & a 21 x m3.xlarge HDFS cluster</a> 242 </td> 243 </tr> 244 <tr> 245 <td>3.54</td> 246 <td>6.29</td> 247 <td>7.66</td> 248 <td>11.92</td> 249 <td> 250 <a class="reference external" href="billion-nyc-taxi-rides-spark-2-4-versus-presto-214.html"><strong>Presto</strong> 0.214 & a 21 x m3.xlarge HDFS cluster</a> 251 </td> 252 </tr> 253 <tr> 254 <td>4</td> 255 <td>4</td> 256 <td>10</td> 257 <td>21</td> 258 <td> 259 <a class="reference external" href="50-node-presto-cluster-dataproc.html"><strong>Presto</strong> & a 50-node n1-standard-4 cluster</a> 260 </td> 261 </tr> 262 <tr> 263 <td>4.88</td> 264 <td>11</td> 265 <td>12</td> 266 <td>15</td> 267 <td> 268 <a class="reference external" href="billion-nyc-taxi-rides-ec2-versus-emr.html#presto-on-emr-benchmark-results"><strong>Presto</strong> 0.188 & a 21-node m3.xlarge cluster</a> 269 </td> 270 </tr> 271 <tr> 272 <td>6.41</td> 273 <td>6.19</td> 274 <td>6.09</td> 275 <td>6.63</td> 276 <td> 277 <a class="reference external" href="billion-nyc-taxi-rides-aws-athena.html"><strong>Amazon Athena</strong></a> 278 </td> 279 </tr> 280 <tr> 281 <td>8.1</td> 282 <td>18.18</td> 283 <td>n/a</td> 284 <td>n/a</td> 285 <td> 286 <a class="reference external" href="billion-nyc-taxi-rides-elasticsearch.html"><strong>Elasticsearch</strong> (heavily tuned)</a> 287 </td> 288 </tr> 289 <tr> 290 <td>10.19</td> 291 <td>8.134</td> 292 <td>19.624</td> 293 <td>85.942</td> 294 <td> 295 <a class="reference external" href="billion-nyc-taxi-rides-spark-2-1-0-emr.html"><strong>Spark</strong> 2.1 & an 11 x m3.xlarge HDFS cluster</a> 296 </td> 297 </tr> 298 <tr> 299 <td>11</td> 300 <td>10</td> 301 <td>21</td> 302 <td>31</td> 303 <td> 304 <a class="reference external" href="faster-queries-google-cloud-dataproc.html"><strong>Presto</strong> & a 10-node n1-standard-4 cluster</a> 305 </td> 306 </tr> 307 <tr> 308 <td>11</td> 309 <td>14</td> 310 <td>16</td> 311 <td>22</td> 312 <td> 313 <a class="reference external" href="billion-nyc-taxi-rides-ec2-versus-emr.html#presto-benchmark-results"><strong>Presto</strong> 0.188 & a single-node i3.8xlarge</a> 314 </td> 315 </tr> 316 <tr> 317 <td>14.389</td> 318 <td>32.148</td> 319 <td>33.448</td> 320 <td>67.312</td> 321 <td> 322 <a class="reference external" href="billion-nyc-taxi-vertica.html"><strong>Vertica</strong> & an Intel Core i5 4670K</a> 323 </td> 324 </tr> 325 <tr> 326 <td>22</td> 327 <td>25</td> 328 <td>27</td> 329 <td>65</td> 330 <td> 331 <a class="reference external" href="billion-nyc-taxi-rides-ec2-versus-emr.html#spark-benchmark-results"><strong>Spark</strong> 2.3.0 & a single-node i3.8xlarge</a> 332 </td> 333 </tr> 334 <tr> 335 <td>28</td> 336 <td>31</td> 337 <td>33</td> 338 <td>80</td> 339 <td> 340 <a class="reference external" href="billion-nyc-taxi-rides-ec2-versus-emr.html#spark-sql-on-emr-benchmark-results"><strong>Spark</strong> 2.2.1 & a 21-node m3.xlarge cluster</a> 341 </td> 342 </tr> 343 <tr> 344 <td>34.48</td> 345 <td>63.3</td> 346 <td>n/a</td> 347 <td>n/a</td> 348 <td> 349 <a class="reference external" href="all-billion-nyc-taxi-rides-elasticsearch.html"><strong>Elasticsearch</strong> (lightly tuned)</a> 350 </td> 351 </tr> 352 <tr> 353 <td>35</td> 354 <td>39</td> 355 <td>64</td> 356 <td>81</td> 357 <td> 358 <a class="reference external" href="billion-nyc-taxi-rides-s3-vs-hdfs.html"><strong>Presto</strong> & a 5-node m3.xlarge HDFS cluster</a> 359 </td> 360 </tr> 361 <tr> 362 <td>43</td> 363 <td>45</td> 364 <td>27</td> 365 <td>44</td> 366 <td> 367 <a class="reference external" href="50-node-emr-cluster-presto.html"><strong>Presto</strong> & a 50-node m3.xlarge cluster w/ S3</a> 368 </td> 369 </tr> 370 <tr> 371 <td>152</td> 372 <td>175</td> 373 <td>235</td> 374 <td>368</td> 375 <td> 376 <a class="reference external" href="billion-nyc-taxi-rides-postgresql.html"><strong>PostgreSQL</strong> 9.5 & cstore_fdw</a> 377 </td> 378 </tr> 379 <tr> 380 <td>264</td> 381 <td>313</td> 382 <td>620</td> 383 <td>961</td> 384 <td> 385 <a class="reference external" href="billion-nyc-taxi-rides-spark-emr.html"><strong>Spark</strong> 1.6 & a 5-node m3.xlarge cluster w/ S3</a> 386 </td> 387 </tr> 388 <tr> 389 <td>448</td> 390 <td>797</td> 391 <td>1811</td> 392 <td>3286</td> 393 <td> 394 <a class="reference external" href="billion-nyc-taxi-rides-sqlite-parquet-hdfs.html"><strong>SQLite</strong> 3, Parquet & HDFS</a> 395 </td> 396 </tr> 397 <tr> 398 <td>1103</td> 399 <td>1198</td> 400 <td>2278</td> 401 <td>6446</td> 402 <td> 403 <a class="reference external" href="billion-nyc-taxi-rides-spark-raspberry-pi.html"><strong>Spark</strong> 2.2 & a 3-node Raspberry Pi cluster</a> 404 </td> 405 </tr> 406 <tr> 407 <td>
40731193</td> 408 <td>NR</td> 409 <td>NR</td> 410 <td>NR</td> 411 <td> 412 <a class="reference external" href="billion-nyc-taxi-rides-sqlite-parquet-hdfs.html#comparing-to-sqlite-s-internal-format"><strong>SQLite</strong> 3, Internal File Format</a> 413 </td> 414 </tr> 415 </tbody> 416 </table> 417</div><ul class="simple"> 418<li>NR is short for "Not Run".</li> 419</ul> 420 421 </div> 422 </article> 423 424 <footer> 425 <p><a href="./" class="button_accent">← Back to Index</a></p> 426 </footer> 427 428 <div id="ending_message"> 429 <p>Copyright © 2014 - 2026 Mark Litwintschik. This site's template is based off a <a href="https://github.com/giulivo/pelican-svbhack" target="_blank">template</a> by Giulio Fidente.</p> 430 </div> 431 </main> 432</body> 433</html>
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.