Show HN: Query 1.6B rows in milliseconds, live
Author here.<p>A few weeks ago, we wrote about how we implemented SIMD instructions to aggregate a billion rows in milliseconds [1] thanks in great part to Agner Fog’s VCL library [2]. Although the initial scope was limited to table-wide aggregates into a unique scalar value, this was a first step towards very promising results on more complex aggregations. With the latest release of QuestDB, we are extending this level of performance to key-based aggregations.<p>To do this, we implemented Google’s fast hash table aka “Swisstable” [3] which can be found in the Abseil library [4]. In all modesty, we also found room to slightly accelerate it for our use case. Our version of Swisstable is dubbed “rosti”, after the traditional Swiss dish [5]. There were also a number of improvements thanks to techniques suggested by the community such as prefetch (which interestingly turned out to have no effect in the map code itself) [6]. Besides C++, we used our very own queue system written in Java to parallelise the execution [7].<p>The results are remarkable: millisecond latency on keyed aggregations that span over billions of rows.<p>We thought it could be a good occasion to show our progress by making this latest release available to try online with a pre-loaded dataset. It runs on an AWS instance using 23 threads. The data is stored on disk and includes a 1.6billion row NYC taxi dataset, 10 years of weather data with around 30-minute resolution and weekly gas prices over the last decade. The instance is located in London, so folks outside of Europe may experience different network latencies. The server-side time is reported as “Execute”.<p>We provide sample queries to get started, but you are encouraged to modify them. However, please be aware that not every type of query is fast yet. Some are still running under an old single-threaded model. If you find one of these, you’ll know: it will take minutes instead of milliseconds. But bear with us, this is just a matter of time before we make these instantaneous as well. Next in our crosshairs is time-bucket aggregations using the SAMPLE BY clause.<p>If you are interested in checking out how we did this, our code is available open-source [8]. We look forward to receiving your feedback on our work so far. Even better, we would love to hear more ideas to further improve performance. Even after decades in high performance computing, we are still learning something new every day.<p>[1] <a href="https://questdb.io/blog/2020/04/02/using-simd-to-aggregate-billions-of-rows-per-second" rel="nofollow">https://questdb.io/blog/2020/04/02/using-simd-to-aggregate-b...</a><p>[2] <a href="https://www.agner.org/optimize/vectorclass.pdf" rel="nofollow">https://www.agner.org/optimize/vectorclass.pdf</a><p>[3] <a href="https://www.youtube.com/watch?v=ncHmEUmJZf4" rel="nofollow">https://www.youtube.com/watch?v=ncHmEUmJZf4</a><p>[4] <a href="https://github.com/abseil/abseil-cpp" rel="nofollow">https://github.com/abseil/abseil-cpp</a><p>[5] <a href="https://github.com/questdb/questdb/blob/master/core/src/main/c/share/rosti.h" rel="nofollow">https://github.com/questdb/questdb/blob/master/core/src/main...</a><p>[6] <a href="https://github.com/questdb/questdb/blob/master/core/src/main/c/share/vec_agg.cpp#L155" rel="nofollow">https://github.com/questdb/questdb/blob/master/core/src/main...</a><p>[7] <a href="https://questdb.io/blog/2020/03/15/interthread" rel="nofollow">https://questdb.io/blog/2020/03/15/interthread</a><p>[8] <a href="https://github.com/questdb/questdb" rel="nofollow">https://github.com/questdb/questdb</a>