OLAP demo: GROUP BY with zero load, zero sort key, zero index
You are looking at Bitcoin value-flow graphBitcoin value-flow table · GROUP BY, no index in one Semurg substrate. Ask it to GROUP BY the Bitcoin value-flows and it runs the
real query live on this box, on data you can
download and re-run yourself.
Click an example in the agent rail, or change the group key, e.g. GROUP BY block height. Every run is measured end to end, on
this box, on the real dataset. Nothing is pre-computed.
The analytics headline query, SELECT key, count(*) GROUP BY key, run as a mass-scan fold over the Bitcoin value-flow table living in this one substrate. No load step, no ORDER key, no compression build, no second system. Measured live, cold / warm / hot.
the columnar OLAP leader
LOAD, SORT, COMPRESS, INDEX FIRST. A columnar engine answers its bucketed query at hot speed only after it has loaded, sorted, compressed and indexed the table, and it stands as a second copy of the data next to the operational store.
semurg, one engine
Zero load, zero index, zero sort key. The GROUP BY folds the live Bitcoin value-flow table in place, the moment rows land, on the same substrate that took the write.
We do not beat its hot tier cold. We delete the load step, the index build and the second copy of the data.
The live Bitcoin value-flow table, aggregated in place. Measured live on this box. Re-pull the chain over Bitcoin P2P and re-run it yourself.
2.51Brows/s folded · SIMD aggregation, warm
34.87Mcold rows/s
2.51Bwarm rows/s
5.05Bhot rows/s
799.66Mtable rows
11groups
22.93 saggregation time
0indexes built
0sort keys
GROUP BY output-value bucket (top groups):
output-value bucket = 1–9 sat → 155.05M·
output-value bucket = 0 (OP_RETURN) → 148.53M·
output-value bucket = 0.001–0.01 BTC → 119.58M·
output-value bucket = 0.01–0.1 BTC → 91.12M·
output-value bucket = 10k–100k sat → 66.75M·
cold 22.93 s sweep · warm 6.4 ms · hot 348.0 µs · no load · no sort key · no index · no merges
the answer
GROUP BY output-value bucket
group
count
output-value bucket = 1–9 sat
155.05M
output-value bucket = 0 (OP_RETURN)
148.53M
output-value bucket = 0.001–0.01 BTC
119.58M
output-value bucket = 0.01–0.1 BTC
91.12M
output-value bucket = 10k–100k sat
66.75M
exact per-value counts, folded live over 799.66M rows, no pre-aggregation, no index
2.51Brows/s
GROUP BY folded 799.66M rows live, zero load, zero index
100xCommanding
collapse: HTAP on one copy
no load, no sort key, no second copy
hit-rate + reads/s · cold / warm / hot
cold 0.1%
warm 0.7%
hot 99.2%
cold 34.9M/s
warm 2.51B/s
hot 5.05B/s
reads/s at each tier · cold straight off NVMe · warm from the RAM ring · hot from CPU cache
RAM utilization · working-set vs dataset
5.2 GB anonymous RAM (the pipe) · 5.4 GB resident incl. reclaimable page cache · 169.73GB on disk · RAM is the pipe, not the store
where the data was when the CPU read it
COLD on disk, straight off the NVMe
WARM in the RAM pipe (the conveyor ring)
HOT already in CPU cache
Nearly all hot and almost no cold, with a tiny resident working-set over a large
on-disk dataset, is the whole point: disk-capacity storage running at cache speed. RAM
here is a pipe, not the store. A normal engine only reaches this speed by first loading the
entire dataset into expensive RAM. This one keeps the truth on disk and streams only what the
query touches, so the resident bar above stays small while the reads stay hot.
Honest: a purpose-built columnar engine, once it has loaded, sorted, compressed and indexed the table, answers its fixed bucketed query at hot speed and can match or beat our hot tier on that one query. We do not dispute that, and we never claim our cold beats their hot. The same substrate that took the operational write runs the analytic rollup, live, the moment the rows arrive, and the same engine also serves graph, full-text, vectors and key-value in one pass.