100M-Row Query Benchmark

ReferencePerformanceBenchmarks

This page documents a single recorded run of AetheriusDB’s 31-query analytical suite over a 100,676,159-row dataset — ten times the size of the 10M-Row Query Benchmark — with the exact SQL of every query and the measured latency alongside how many rows each query actually has to process.

Run identity

AetheriusDB 0.1.105, pgwire protocol via psql, single client, dedicated daemon. Host: Apple M4 (10 cores), 16 GB RAM, macOS 26.6. Date: 2026-08-25. All 31 queries completed under a 10-second cap; none were killed and none returned an empty result.

Test environment

CPU	Apple M4 — 10 cores: 4 performance + 6 efficiency
Memory	16 GB unified
Storage	External SSD — the 14 GB data directory does not sit on the internal NVMe, unlike the 10M run
OS	macOS 26.6 (build 25G72), arm64
Build	AetheriusDB 0.1.105, release profile, dedicated daemon on port 5690
Client	psql (pgwire), loopback, single client, one query at a time
Memory ceiling	AETHERIUS_MAX_MEMORY_BYTES = 8 GiB
Cap	statement_timeout=10000 and daemon --query-watchdog-ms 10000
Host at run start	load averages: 3.03 6.89 6.57; mem_free=61% pageouts=52446 ; swapfile=total = 9216.00M used = 8212.19M free = 1003.81M (encrypted)

The storage difference is not cosmetic

The 10M run used the internal NVMe; this one uses an external volume. Scan-heavy queries feel that most. Do not read a 10M-vs-100M delta as pure scaling — the storage path differs too.

The dataset

Table	Rows
order_items	64,676,159
orders	30,000,000
customers	5,000,000
products	1,000,000
Total	100,676,159

Row counts come from the generator’s own manifest.json, and the live database returns exactly these figures. On disk the data directory is 14 GB.

A gx_ecom graph index is declared over the four foreign-key links before the suite runs, and the run verifies it registered — a plan must actually report a declared edge — rather than trusting the DDL’s return code.

How the numbers were produced

Each query runs five times. Run 0 is the cold sample; the reported warm figure is the median of runs 1–4.

Which clock — this matters more than it sounds

Two clocks were recorded per execution, and only one belongs in a benchmark:

• Query time (reported here) — psql’s own \timing, the query round-trip excluding connection setup.
• Wall clock — the whole psql invocation, including a fresh TCP connection and authentication.

On this host a connection costs 100–200 ms, while the server answers SELECT 1 in 0.08 ms. This suite’s wall-clock median is 170 ms; its query-time median is 63 ms. For point lookups the gap is enormous — Q01 is 0.26 ms of engine time inside a 110 ms wall-clock call, and that difference is entirely client overhead.

Every figure on this page is query time. Quoting the wall figures would describe psql, not AetheriusDB.

Results

Sorted by query id. Warm is the median of runs 1–4; cold is run 0.

Query	Cold ms	Warm ms	Input rows	Bytes out	What it measures
Q01	118	0.26	5,000,000	65	PK lookup on customers (100 random keys)
Q02	365	0.40	30,000,000	607	Secondary-index lookup: all orders for one customer
Q03	209	0.90	100,676,159	94	FIND ONE ITEM FOR A CUSTOMER — 4-table hop customers→orders→order_items→products, latest first
Q04	108	105	65,676,159	59	Same needle via denormalized order_items.customer_id (1 hop) — isolates the graph-index gain
Q05	69	14	5,000,000	56	Full-scan filter on UNINDEXED email (row-store vs columnar asymmetry, not a speed test)
Q05b	17	16	5,000,000	20	Companion: COUNT over an unindexed LIKE prefix scan
Q06	1,167	465	35,000,000	126	2-table join: orders ⋈ customers, count by tier
Q07	5,587	1,153	99,676,159	465	3-table join: order_items ⋈ orders ⋈ customers, revenue by country
Q08	2,338	1,517	100,676,159	829	4-table join: revenue by (tier, category) — the full star
Q09	366	303	100,676,159	146	4-table join WITH selective filter — tests predicate pushdown
Q10	689	688	35,000,000	22	Anti-join: customers with zero orders (LEFT JOIN … IS NULL)
Q11	2,504	2,483	64,676,159	280	Self-join: product pairs bought together, top 20 (deliberately brutal)
Q12	179	47	100,676,159	3,308	3-table join + LIMIT 100 (the known 1910 ms weak spot)
Q13	15	0.91	64,676,159	23	COUNT(*) over order_items (largest table)
Q14	0.77	0.22	30,000,000	189	Single-key GROUP BY status (6 buckets)
Q15	74	63	5,000,000	2,127	2-key GROUP BY (country, tier) — ~80 buckets
Q16	47	30	30,000,000	2,245	High-cardinality GROUP BY customer_id → top 100 by revenue
Q17	96	27	30,000,000	1,080	GROUP BY … HAVING on an aggregate (tier-scaled cut)
Q18	1,010	990	30,000,000	812	COUNT(DISTINCT customer_id) per month
Q19	1,318	994	95,676,159	259	Conditional aggregation: return rate by category (4-table)
Q20	0.46	0.17	30,000,000	33	Timestamp BETWEEN — one-month slice
Q21	0.16	0.17	30,000,000	183	Timestamp BETWEEN — 12-month slice, grouped
Q22	0.14	0.18	30,000,000	170	Integer range on materialized order_ym (index-friendly)
Q23	1,094	876	35,000,000	114	Compound: range + IN list + LIKE prefix across a join
Q24	126	92	30,000,000	4,905	Top-N: ORDER BY total_cents DESC LIMIT 100 over all orders
Q25	2,215	1,950	30,000,000	4,607	ROW_NUMBER per customer → each customer’s latest order
Q26	431	366	65,676,159	466	RANK/DENSE_RANK — top 3 products by revenue per category
Q27	39	22	30,000,000	1,798	LAG/LEAD — month-over-month revenue delta
Q28	22	22	30,000,000	1,375	Running total of monthly revenue (ROWS UNBOUNDED PRECEDING)
Q29	1,674	1,654	5,000,000	213	NTILE(10) — customer deciles by lifetime value
Q30	1,172	1,156	100,676,159	2,120	Window over a 4-table join — per-country customer rank

At a glance

Metric	Value (warm query time)
Median	63 ms
Mean	485 ms
Slowest	2,483 ms (Q11)
Sum of all 31	15.04 s
Under 1 ms	8
Under 100 ms	17
Over 1 s	6
Killed at the cap	0
Empty results	0

The distribution is bimodal, and that is the honest shape of it: 8 queries answer in under a millisecond — point lookups and indexed range scans that touch a bounded number of rows — while 6 take over a second, all of them multi-table joins or window functions over tens of millions of rows. There is very little in between.

How to read these numbers

This is one run on one machine. It is not a certified benchmark score, and the host is a laptop with an external SSD, not server hardware.

Cold and warm differ, and both are shown. Cold figures include first-touch page-cache misses against the external volume. A single cold sample is not a stable measurement — which is why the warm figure is a median of four and the cold is labelled separately rather than averaged in.

Input rows are the sum of the base tables each query reads, not the rows surviving its filters. A query with 100 M input rows and a 1-row answer is doing selective work, not scanning 100 M rows to completion.

Bytes out, not rows out. The harness records the result payload size, which is what it can measure without re-parsing every result set.

The queries

Every query below is the exact SQL executed, taken from the run’s own capture.

Q01 — PK lookup on customers (100 random keys)

0.26 ms warm · cold 118 ms · 5,000,000 input rows · 65 bytes returned

SELECT customer_id, email, country, tier, lifetime_value_cents FROM customers WHERE customer_id = 1666666

Q02 — Secondary-index lookup: all orders for one customer

0.40 ms warm · cold 365 ms · 30,000,000 input rows · 607 bytes returned

SELECT order_id, order_ts, status, total_cents FROM orders WHERE customer_id = 2500007 ORDER BY order_id

Q03 — FIND ONE ITEM FOR A CUSTOMER — 4-table hop customers→orders→order_items→products, latest first

0.90 ms warm · cold 209 ms · 100,676,159 input rows · 94 bytes returned

SELECT c.customer_id, c.tier, o.order_id, o.order_ts, p.product_id, p.name, oi.quantity, oi.line_total_cents FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id WHERE c.customer_id = 2500007 ORDER BY o.order_ts DESC, oi.order_item_id ASC LIMIT 1

Q04 — Same needle via denormalized order_items.customer_id (1 hop) — isolates the graph-index gain

105 ms warm · cold 108 ms · 65,676,159 input rows · 59 bytes returned

SELECT oi.order_item_id, oi.order_id, p.product_id, p.name, oi.quantity, oi.line_total_cents FROM order_items oi JOIN products p ON p.product_id = oi.product_id WHERE oi.customer_id = 2500007 ORDER BY oi.order_item_id DESC LIMIT 1

Q05 — Full-scan filter on UNINDEXED email (row-store vs columnar asymmetry, not a speed test)

14 ms warm · cold 69 ms · 5,000,000 input rows · 56 bytes returned

SELECT customer_id, email, tier FROM customers WHERE email = 'user1666666@mail12.example'

Q05b — Companion: COUNT over an unindexed LIKE prefix scan

16 ms warm · cold 17 ms · 5,000,000 input rows · 20 bytes returned

SELECT COUNT(*) FROM customers WHERE email LIKE 'user123%'

Q06 — 2-table join: orders ⋈ customers, count by tier

465 ms warm · cold 1,167 ms · 35,000,000 input rows · 126 bytes returned

SELECT c.tier, COUNT(*) AS orders_n, SUM(o.total_cents) AS revenue FROM orders o JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.tier ORDER BY c.tier

Q07 — 3-table join: order_items ⋈ orders ⋈ customers, revenue by country

1,153 ms warm · cold 5,587 ms · 99,676,159 input rows · 465 bytes returned

SELECT c.country, COUNT(*) AS lines_n, SUM(oi.line_total_cents) AS revenue FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.country ORDER BY c.country

Q08 — 4-table join: revenue by (tier, category) — the full star

1,517 ms warm · cold 2,338 ms · 100,676,159 input rows · 829 bytes returned

SELECT c.tier, p.category, COUNT(*) AS lines_n, SUM(oi.line_total_cents) AS revenue FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN customers c ON c.customer_id = o.customer_id JOIN products p ON p.product_id = oi.product_id GROUP BY c.tier, p.category ORDER BY c.tier, p.category

Q09 — 4-table join WITH selective filter — tests predicate pushdown

303 ms warm · cold 366 ms · 100,676,159 input rows · 146 bytes returned

SELECT p.category, COUNT(*) AS lines_n, SUM(oi.line_total_cents) AS revenue FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN customers c ON c.customer_id = o.customer_id JOIN products p ON p.product_id = oi.product_id WHERE o.order_ym BETWEEN 202401 AND 202403 AND c.tier = 'platinum' AND o.status = 'delivered' GROUP BY p.category ORDER BY p.category

Q10 — Anti-join: customers with zero orders (LEFT JOIN … IS NULL)

688 ms warm · cold 689 ms · 35,000,000 input rows · 22 bytes returned

SELECT COUNT(*) AS customers_without_orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL

Q11 — Self-join: product pairs bought together, top 20 (deliberately brutal)

2,483 ms warm · cold 2,504 ms · 64,676,159 input rows · 280 bytes returned

SELECT a.product_id AS p1, b.product_id AS p2, COUNT(*) AS pair_n FROM order_items a JOIN order_items b ON b.order_id = a.order_id AND a.product_id < b.product_id GROUP BY a.product_id, b.product_id ORDER BY COUNT(*) DESC, a.product_id ASC, b.product_id ASC LIMIT 20

Q12 — 3-table join + LIMIT 100 (the known 1910 ms weak spot)

47 ms warm · cold 179 ms · 100,676,159 input rows · 3,308 bytes returned

SELECT o.order_id, c.tier, p.name, oi.line_total_cents FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN customers c ON c.customer_id = o.customer_id JOIN products p ON p.product_id = oi.product_id ORDER BY oi.order_item_id LIMIT 100

Q13 — COUNT(*) over order_items (largest table)

0.91 ms warm · cold 15 ms · 64,676,159 input rows · 23 bytes returned

SELECT COUNT(*) FROM order_items

Q14 — Single-key GROUP BY status (6 buckets)

0.22 ms warm · cold 0.77 ms · 30,000,000 input rows · 189 bytes returned

SELECT status, COUNT(*) AS n, SUM(total_cents) AS revenue FROM orders GROUP BY status ORDER BY status

Q15 — 2-key GROUP BY (country, tier) — ~80 buckets

63 ms warm · cold 74 ms · 5,000,000 input rows · 2,127 bytes returned

SELECT c.country, c.tier, COUNT(*) AS n, SUM(c.lifetime_value_cents) AS ltv FROM customers c GROUP BY c.country, c.tier ORDER BY c.country, c.tier

Q16 — High-cardinality GROUP BY customer_id → top 100 by revenue

30 ms warm · cold 47 ms · 30,000,000 input rows · 2,245 bytes returned

SELECT customer_id, SUM(total_cents) AS revenue, COUNT(*) AS orders_n FROM orders GROUP BY customer_id ORDER BY SUM(total_cents) DESC, customer_id ASC LIMIT 100

Q17 — GROUP BY … HAVING on an aggregate (tier-scaled cut)

27 ms warm · cold 96 ms · 30,000,000 input rows · 1,080 bytes returned

SELECT order_ym, SUM(total_cents) AS revenue, COUNT(*) AS n FROM orders GROUP BY order_ym HAVING SUM(total_cents) > 28110000000 ORDER BY order_ym

Q18 — COUNT(DISTINCT customer_id) per month

990 ms warm · cold 1,010 ms · 30,000,000 input rows · 812 bytes returned

SELECT order_ym, COUNT(DISTINCT customer_id) AS uniq_customers, COUNT(*) AS orders_n FROM orders GROUP BY order_ym ORDER BY order_ym

Q19 — Conditional aggregation: return rate by category (4-table)

994 ms warm · cold 1,318 ms · 95,676,159 input rows · 259 bytes returned

SELECT p.category, SUM(CASE WHEN o.status = 'returned' THEN oi.line_total_cents ELSE 0 END) AS returned_cents, SUM(oi.line_total_cents) AS total_cents, COUNT(*) AS lines_n FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN products p ON p.product_id = oi.product_id GROUP BY p.category ORDER BY p.category

Q20 — Timestamp BETWEEN — one-month slice

0.17 ms warm · cold 0.46 ms · 30,000,000 input rows · 33 bytes returned

SELECT COUNT(*) AS n, SUM(total_cents) AS revenue FROM orders WHERE order_ts BETWEEN TIMESTAMP '2024-06-01 00:00:00' AND TIMESTAMP '2024-06-30 23:59:59'

Q21 — Timestamp BETWEEN — 12-month slice, grouped

0.17 ms warm · cold 0.16 ms · 30,000,000 input rows · 183 bytes returned

SELECT status, COUNT(*) AS n, SUM(total_cents) AS revenue FROM orders WHERE order_ts BETWEEN TIMESTAMP '2024-01-01 00:00:00' AND TIMESTAMP '2024-12-31 23:59:59' GROUP BY status ORDER BY status

Q22 — Integer range on materialized order_ym (index-friendly)

0.18 ms warm · cold 0.14 ms · 30,000,000 input rows · 170 bytes returned

SELECT order_ym, COUNT(*) AS n, SUM(total_cents) AS revenue FROM orders WHERE order_ym BETWEEN 202401 AND 202406 GROUP BY order_ym ORDER BY order_ym

Q23 — Compound: range + IN list + LIKE prefix across a join

876 ms warm · cold 1,094 ms · 35,000,000 input rows · 114 bytes returned

SELECT o.channel, COUNT(*) AS n, SUM(o.total_cents) AS revenue FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.order_ym BETWEEN 202401 AND 202412 AND o.status IN ('paid', 'shipped', 'delivered') AND c.country IN ('US', 'IN', 'GB') AND c.email LIKE 'user1%' GROUP BY o.channel ORDER BY o.channel

Q24 — Top-N: ORDER BY total_cents DESC LIMIT 100 over all orders

92 ms warm · cold 126 ms · 30,000,000 input rows · 4,905 bytes returned

SELECT order_id, customer_id, order_ts, total_cents FROM orders ORDER BY total_cents DESC, order_id ASC LIMIT 100

Q25 — ROW_NUMBER per customer → each customer’s latest order

1,950 ms warm · cold 2,215 ms · 30,000,000 input rows · 4,607 bytes returned

SELECT customer_id, order_id, order_ts, total_cents FROM ( SELECT customer_id, order_id, order_ts, total_cents, row_number() OVER (PARTITION BY customer_id ORDER BY order_ts DESC, order_id DESC) AS rn FROM orders ) z WHERE rn = 1 ORDER BY customer_id LIMIT 100

Q26 — RANK/DENSE_RANK — top 3 products by revenue per category

366 ms warm · cold 431 ms · 65,676,159 input rows · 466 bytes returned

SELECT category, product_id, revenue, rk FROM ( SELECT p.category, p.product_id, SUM(oi.line_total_cents) AS revenue, rank() OVER (PARTITION BY p.category ORDER BY SUM(oi.line_total_cents) DESC) AS rk FROM order_items oi JOIN products p ON p.product_id = oi.product_id GROUP BY p.category, p.product_id ) z WHERE rk <= 3 ORDER BY category, rk, product_id

Q27 — LAG/LEAD — month-over-month revenue delta

22 ms warm · cold 39 ms · 30,000,000 input rows · 1,798 bytes returned

SELECT order_ym, SUM(total_cents) AS revenue, lag(SUM(total_cents), 1) OVER (ORDER BY order_ym) AS prev_month, lead(SUM(total_cents), 1) OVER (ORDER BY order_ym) AS next_month FROM orders GROUP BY order_ym ORDER BY order_ym

Q28 — Running total of monthly revenue (ROWS UNBOUNDED PRECEDING)

22 ms warm · cold 22 ms · 30,000,000 input rows · 1,375 bytes returned

SELECT order_ym, SUM(total_cents) AS revenue, SUM(SUM(total_cents)) OVER (ORDER BY order_ym ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative FROM orders GROUP BY order_ym ORDER BY order_ym

Q29 — NTILE(10) — customer deciles by lifetime value

1,654 ms warm · cold 1,674 ms · 5,000,000 input rows · 213 bytes returned

SELECT decile, COUNT(*) AS n, MIN(lifetime_value_cents) AS lo, MAX(lifetime_value_cents) AS hi FROM ( SELECT customer_id, lifetime_value_cents, ntile(10) OVER (ORDER BY lifetime_value_cents DESC, customer_id ASC) AS decile FROM customers ) z GROUP BY decile ORDER BY decile

Q30 — Window over a 4-table join — per-country customer rank

1,156 ms warm · cold 1,172 ms · 100,676,159 input rows · 2,120 bytes returned

SELECT country, customer_id, revenue, rk FROM ( SELECT c.country, c.customer_id, SUM(oi.line_total_cents) AS revenue, rank() OVER (PARTITION BY c.country ORDER BY SUM(oi.line_total_cents) DESC) AS rk FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN customers c ON c.customer_id = o.customer_id JOIN products p ON p.product_id = oi.product_id GROUP BY c.country, c.customer_id ) z WHERE rk <= 5 ORDER BY country, rk, customer_id

10M-Row Query Benchmark →

AetheriusDB Benchmarks →