10M-Row Query Benchmark

ReferencePerformanceBenchmarks

This page documents a single recorded run of AetheriusDB’s 31-query analytical suite over a 10-million-row dataset: the schema it runs against, the exact SQL of every query, and the measured latency alongside how many rows each query actually has to process.

Latency alone is not interpretable — 500 ms is excellent over 10 million rows and poor over 500. Every result below carries its input row count so the numbers mean something.

A 10× larger run exists

The same 31-query suite has since been run over 100,676,159 rows — see 100M-Row Query Benchmark. Note that the 100M run stores its data on an external volume and measures with psql (pgwire), where this page uses internal NVMe and the native protocol, so the two are not a clean like-for-like scaling comparison.

This run differs from the 0.1.94 one in two ways besides the build

The 0.1.94 figures were taken over the native protocol on a data directory on internal NVMe. This run uses pgwire (psql) against a directory on an external SSD. Both report query round-trip excluding connection setup, so the clock is comparable — but storage and protocol are not held constant, and part of any delta belongs to them rather than to the engine.

Run identity

AetheriusDB 0.1.105, pgwire via psql, single client, dedicated daemon. Host: Apple M4 (10 cores), 16 GB RAM, macOS 26.6. Date: 2026-08-27. All 31 queries completed, none over 1 second. Times are query round-trip (psql \timing), excluding connection setup.

Test environment

Earlier benchmark pages did not record the hardware, which made their numbers impossible to reproduce or compare. This run does.

CPU	Apple M4 — 10 cores: 4 performance + 6 efficiency
Memory	16 GB unified
Storage	Internal NVMe SSD (data directory on the boot volume)
OS	macOS 26.6 (build 25G72), arm64
Build	AetheriusDB 0.1.105, release profile, dedicated daemon (not the shared installed daemon — see below)
Client	psql (pgwire), loopback
Concurrency	Single client, one query at a time

Why a dedicated daemon — measured, not assumed

Earlier runs on this page used the host’s installed daemon, which co-hosts several other databases, each with background maintenance tickers. That is not a neutral choice: on 0.1.94 the same binary, data and layout measured the join-heavy queries 2–3× slower on the shared daemon than on an idle dedicated one (Q12: 924 ms shared vs 403 ms dedicated in a COPY-loaded probe). Scan-class queries were far less affected. This run therefore uses a dedicated daemon so the numbers attribute to the engine, not to co-tenant load — and the comparison with earlier pages carries that caveat.

Memory footprint

Dataset on disk	971 MB (fresh data directory, 10,068,831 rows — earlier pages reported 4.1 GB from an aged directory carrying historical container segments)
Encoded working set, all four tables	760.7 MB — from _sys_cube_residency, 0 cubes spilled
Cube layout	order_items 8 cubes, orders 8, customers 4, products 2 — one ~64 MiB cube per loaded part file (earlier runs sealed each table as a single cube)
Daemon resident set, after load + index build	2.51 GB
Daemon resident set, after the suite	5.46 GB

So the whole suite runs inside roughly 5.5 GB of RAM against 971 MB of on-disk data — the working set fits comfortably on a 16 GB host, and every figure on this page is therefore a warm-cache figure. Nothing here characterises behaviour when the data exceeds memory.

Why RSS is the wrong number here, and what replaced it

The daemon reports 2.51 GB resident after the load and 5.46 GB after the suite, against a working set the engine accounts at 760.7 MB. Those are not inconsistent: cube storage is memory-mapped and faulted in lazily, so RSS measures pages touched so far, not what the engine holds. Earlier versions of this page quoted RSS as a residency figure, which is why the numbers never quite added up.

_sys_cube_residency, new in 0.1.84, reports the engine’s own accounting instead. It walks the sealed-cube chain and reads each cube’s recorded footprint — it never materialises columns, so querying it cannot itself pull a spilled cube back off disk.

The on-disk figure includes the row-oriented persistence form and per-table containers, not just the columnar cubes the query path reads.

Per-query CPU utilisation is not published here yet. This run finally had the quiescent dedicated daemon those measurements need; wiring per-query CPU sampling into the recorded harness is the remaining step.

The dataset

A synthetic e-commerce schema — four tables, 10,068,831 rows total, joined in a classic star shape (order_items → orders → customers, plus products).

Table	Rows	Columns	Role
order_items	6,468,831	11	Fact table — one row per line item
orders	3,000,000	12	One row per order
customers	500,000	10	Dimension
products	100,000	10	Dimension

Column types that matter for the results:

customers(customer_id BIGINT PK, email TEXT, full_name TEXT, country TEXT,
          city TEXT, tier TEXT, signup_ts TIMESTAMPTZ, signup_ym INTEGER,
          lifetime_value_cents BIGINT, is_active BOOLEAN)

orders(order_id BIGINT PK, customer_id BIGINT, order_ts TIMESTAMPTZ,
       order_ym INTEGER, order_ymd INTEGER, status TEXT, channel TEXT,
       country TEXT, total_cents BIGINT, discount_cents BIGINT,
       item_count INTEGER, jk_customer BIGINT)

order_items(order_item_id BIGINT PK, order_id BIGINT, product_id BIGINT,
            customer_id BIGINT, quantity INTEGER, unit_price_cents BIGINT,
            line_total_cents BIGINT, discount_cents BIGINT,
            jk_order BIGINT, jk_product BIGINT, jk_customer BIGINT)

products(product_id BIGINT PK, sku TEXT, name TEXT, category TEXT,
         subcategory TEXT, brand TEXT, price_cents BIGINT,
         weight_grams INTEGER, rating DOUBLE PRECISION, in_stock BOOLEAN)

Two design choices in this schema are deliberately there to be measured: order_ym is a materialized integer month alongside the raw timestamp (so timestamp-range and integer-range paths can be compared directly), and the jk_* columns are join-key pseudo-columns used by the graph index.

How the numbers were produced

• Load: four CREATE SOURCE TABLE statements over a directory of 22 split CSV part files (8 + 8 + 4 + 2), ingested by the parallel source-table loop: 10,068,831 rows parsed and appended in ~1.1 s. Since 0.1.94 the loader ingests part files in lexicographic order and seals ~64 MiB cubes, so a split load is physically equivalent to one sequential COPY — verified in this run (the first physical rows of every table are rows 1, 2, 3 of its CSV). Constraints, join keys and indexes are built afterwards as a separate timed phase, exactly as before.
• Client: aesql over the native binary protocol — not pgwire. Timing is the server-reported per-statement round-trip from \timing.
• Runs: each query executes once cold, then three times warm. The table reports the cold run and the median of the three warm runs.
• Isolation: every query gets its own client process, so one pathological query cannot take down the run.
• Input rows: taken from the EXPLAIN plan the optimizer actually chose — one Scan: <table> node counts one full pass over that table’s cardinality. This is derived from the plan, not the SQL text, which is why Q11’s self-join correctly reports 12.9 M (order_items scanned twice) rather than 6.5 M.
• Correctness gate: every result set is checksummed and compared against a recorded baseline. All 30 queries with a recorded baseline returned checksums identical to it (Q11 has no baseline — it had never completed before), so the timings below describe runs that produced correct answers.

What “input rows” does and does not mean

It is the logical input — base-table cardinality per scan node. AetheriusDB frequently reads far less: cube-skip pruning, chrono-stripe bounds and projection narrowing can eliminate most of a table before any row is touched. That is exactly why seven queries below return in under a millisecond against millions of logical rows. The engine exposes no per-node actual-row counters, so this is a defensible upper bound — and because it is identical across versions, it makes throughput comparable between runs.

Results

Sorted by query id. Warm is the median of three runs.

Query	Cold ms	Warm ms	Input rows	Bytes out	What it measures
Q01	8.49	0.27	500,000	62	PK lookup on customers (100 random keys)
Q02	17	0.52	3,000,000	597	Secondary-index lookup: all orders for one customer
Q03	4.09	2.24	10,068,831	86	FIND ONE ITEM FOR A CUSTOMER — 4-table hop customers→orders→order_items→products, latest first
Q04	30	30	6,568,831	58	Same needle via denormalized order_items.customer_id (1 hop) — isolates the graph-index gain
Q05	7.82	7.82	500,000	54	Full-scan filter on UNINDEXED email (row-store vs columnar asymmetry, not a speed test)
Q05b	5.83	7.11	500,000	19	Companion: COUNT over an unindexed LIKE prefix scan
Q06	24	24	3,500,000	117	2-table join: orders ⋈ customers, count by tier
Q07	77	63	9,968,831	427	3-table join: order_items ⋈ orders ⋈ customers, revenue by country
Q08	76	70	10,068,831	1,526	4-table join: revenue by (tier, category) — the full star
Q09	109	107	10,068,831	250	4-table join WITH selective filter — tests predicate pushdown
Q10	57	61	3,500,000	21	Anti-join: customers with zero orders (LEFT JOIN … IS NULL)
Q11	404	408	6,468,831	221	Self-join: product pairs bought together, top 20 (deliberately brutal)
Q12	11	11	10,068,831	3,278	3-table join + LIMIT 100 (the known 1910 ms weak spot)
Q13	3.56	0.17	6,468,831	22	COUNT(*) over order_items (largest table)
Q14	0.23	0.20	3,000,000	177	Single-key GROUP BY status (6 buckets)
Q15	7.18	6.90	500,000	1,967	2-key GROUP BY (country, tier) — ~80 buckets
Q16	78	78	3,000,000	1,944	High-cardinality GROUP BY customer_id → top 100 by revenue
Q17	2.82	2.32	3,000,000	980	GROUP BY … HAVING on an aggregate (tier-scaled cut)
Q18	148	152	3,000,000	827	COUNT(DISTINCT customer_id) per month
Q19	67	66	9,568,831	451	Conditional aggregation: return rate by category (4-table)
Q20	0.15	0.17	3,000,000	31	Timestamp BETWEEN — one-month slice
Q21	0.19	0.19	3,000,000	170	Timestamp BETWEEN — 12-month slice, grouped
Q22	0.21	0.17	3,000,000	158	Integer range on materialized order_ym (index-friendly)
Q23	151	146	3,500,000	105	Compound: range + IN list + LIKE prefix across a join
Q24	9.69	10	3,000,000	4,683	Top-N: ORDER BY total_cents DESC LIMIT 100 over all orders
Q25	164	164	3,000,000	4,428	ROW_NUMBER per customer → each customer’s latest order
Q26	34	35	6,568,831	855	RANK/DENSE_RANK — top 3 products by revenue per category
Q27	2.64	2.18	3,000,000	1,790	LAG/LEAD — month-over-month revenue delta
Q28	5.04	2.36	3,000,000	1,379	Running total of monthly revenue (ROWS UNBOUNDED PRECEDING)
Q29	106	107	500,000	203	NTILE(10) — customer deciles by lifetime value
Q30	63	65	10,068,831	1,925	Window over a 4-table join — per-country customer rank

All 31 queries completed in 1.63 s of total warm time, with a median of 11 ms and nothing over 408 ms. Against the previously published 0.1.94 run — same suite, same host, same dataset — the comparable total falls from 12.4 s to 1.63 s. What changed, and what that number does and does not mean, is set out in What changed since 0.1.86.

What changed since 0.1.86

version	total warm, 30 comparable queries	Q11	checksums
0.1.83	30,906 ms	did not complete	30/30
0.1.84	23,711 / 23,837 / 23,887 ms	did not complete	30/30
0.1.85 (not published)	23,104 / 22,287 ms	did not complete	30/30
0.1.86 (previously published)	17,256 / 17,686 ms	did not complete	30/30
0.1.94 (previously published)	12,449 ms	2,052 ms — completes	30/30
0.1.105 (this page)	1,630 ms	408 ms	30/30

The 0.1.105 row is not a clean like-for-like against the rows above it: those were measured over the native protocol on internal NVMe, this one over pgwire on an external SSD. It also carries two fixes found by re-running this very suite — the reverse-seek budget was counting cubes rather than rows (Q24: 13.8 s → 10 ms at this tier), and the INLJ filtered drive cap was too generous (Q23: 11.2 s → 146 ms). Both bugs were invisible at the 100 M tier, where the table geometry differs, which is why the 10 M suite is worth keeping.

The 0.1.87–0.1.94 releases changed four things this suite can see:

Change	Effect on this suite
Morsel-parallel scan + parallel aggregation engaged	Cube-aligned scan decode runs on a shared worker pool, and the parallel aggregation fold and join probe now actually engage on this workload. This is where the scan/aggregate/window class collapsed: Q06 −78 %, Q15 −69 %, Q16 −71 %, Q17 −75 %, Q18 −73 %, Q24 −55 %, Q26 −80 %, Q27 −73 %, Q28 −74 % against 0.1.86.
PTI mirror temporal-filter inference	A WHERE order_ts BETWEEN … spanning complete months is rewritten to the materialized order_ym range and served from the declared PTI … INCLUDE mirror. Q20 and Q21 drop from 1,055 / 1,118 ms to 0.1 / 0.2 ms — they join Q22 in the pre-aggregated tier, and the range-tier narrative below changes accordingly.
A secondary index no longer slows the query it serves	The secondary-lookup executor’s fallback did a full scan plus a per-row Row materialisation to re-check the probe value — measured 2.8× slower than the plain scan it displaced the moment an index existed on the filter column. The fallback is now vectorized with cube-skip pushdown. Q02-class queries keep parity with the scan path instead of regressing.
Cube geometry and load order are now deterministic	Bulk loads previously sealed one cube per COPY statement (a single 275 MB mega-cube for orders — no skip granularity, no scan parallelism) or one per parse chunk (~2 MiB confetti). Both bulk paths now seal to a shared ~64 MiB aCube target, and multi-file loads ingest part files in lexicographic order, so physical row order equals file order. The residency figures below are the direct evidence.

Q11 completing at all (2.05 s warm) is the largest single change; its attribution is discussed in the section that used to document the timeout.

SELECT * FROM _sys_cube_residency;
 table_name  | cubes_total | cubes_spilled | resident_bytes
-------------+-------------+---------------+----------------
 customers   |           4 |             0 |     54,301,343
 order_items |           8 |             0 |    420,474,048
 orders      |           8 |             0 |    274,934,307
 products    |           2 |             0 |     10,946,328

The join tier moved the other way, and this page says so

Five join-heavy queries are slower than the 0.1.86 page: Q08 +10 %, Q09 +29 %, Q12 +21 %, Q23 +38 %, Q30 +12 %. The cause is layout sensitivity, not a code regression: on this same binary and host, a COPY-loaded layout (5 larger cubes for orders) measured Q07 421 / Q08 542 / Q12 403 / Q30 503 ms — all substantially faster than 0.1.86 — while the source-table-loaded 8-cube layout documented here measures slower on exactly that tier. Both runs are recorded in the repository. Pinning the mechanism (per-batch overhead vs cube fill vs residual ordering effects) is open work; until it lands, treat the join tier as layout-dependent within roughly a 2× band and the scan/aggregate tier as stable.

What changed since 0.1.84 (historical)

version	total warm, 30 completed queries	checksums
0.1.83	30,906 ms	30/30
0.1.84 (previously published)	23,711 / 23,837 / 23,887 ms	30/30
0.1.85 (not published)	23,104 / 22,287 ms	30/30
0.1.86 (this page)	17,256 / 17,686 ms	30/30

Almost all of this step is one change, and the engine’s own counters say so rather than the stopwatch. Splitting the suite by query class:

class	0.1.85	0.1.86
join queries (7)	10,347 ms	7,068 ms	−31.7 %
everything else above 1 ms (17)	7,373 ms	7,017 ms	−4.8 %

Only join routing changed, and only the join class moved. Run-to-run drift would have moved both together.

What went into 0.1.85 and 0.1.86:

Change	Effect on this suite
Row-oriented join arms decline on bulk input	The declared-edge pointer-walk executors (LinkStripeJoinExec, JoinGraphNavExec) are row-at-a-time and sit behind Row↔Batch adapters that materialise a Vec<Value> per row on both boundaries. Excellent for a filtered or point serve; on a full-table analytical join they lose to the columnar hash join. A third executor already carried this gate; these two did not. This is the change the class split above attributes the gain to.
Partitioned top-N fusion (0.1.85)	ROW_NUMBER() … WHERE rn <= N no longer sorts every partition in full: one pass, one bounded keep per partition. Q25 3,129 → 895 ms.
BETWEEN reaches the zonemap	A range predicate could not prune a cube for any column of any type — Expr::Between was simply not matched by the pushdown extractor. Little effect on this dataset, whose timestamps are uniformly random across the year so every cube spans it; the mechanism now works at all.
Columnar-ingest cubes carry skip metadata	Cubes written through the columnar/PULSE paths were sealed with an empty filter, whose inverted sentinel made every probe skippable — a wrong-answer bug, not a slow one. This dataset loads via COPY (the row path) and was never affected.

How the gain was attributed. Not by stopwatch. Three counters were added to the engine — routes taken, routes declined, and top-N seeds applied — and read from system.cognitive_metrics after the suite. They showed zero joins on either row-oriented route, and zero FK top-N seeds, which ruled out a change that had been the leading hypothesis. Timing alone could not have separated those: run-to-run spread on this suite reaches 21 % on identical code.

The memory work from 0.1.84 still underpins all of it — the residency view says so directly:

SELECT * FROM _sys_cube_residency;
 table_name  | cubes_total | cubes_spilled | resident_bytes
-------------+-------------+---------------+----------------
 customers   |           1 |             0 |     54,301,343
 order_items |           1 |             0 |    420,474,016
 orders      |           1 |             0 |    274,934,307
 products    |           1 |             0 |     10,946,328

Zero cubes spilled. Under the old 1 GiB probe the working set exceeded the ceiling and scans paid a full re-read from disk; a warm three-table join was measured re-reading 286–417 MB per run. That cost is now gone, which is the kind of change that moves an entire suite rather than one query.

Limits of this comparison

All runs are single-client, warm-cache, on an idle host — but they are different daemon processes started at different times. Run-to-run spread on this suite reaches 21 % on identical code, which is why the headline is quoted against medians and why the per-class split above carries more weight than the total. The conservative reading — the slowest 0.1.86 run against the fastest 0.1.84 run — is −25 %.

Per-query deltas range from −61 % (Q12) to roughly flat. The four sub-millisecond metadata-path queries (Q03, Q13, Q14, Q22) are unchanged because they never touched the data in the first place, and Q09/Q23 barely moved because their joins were not on the affected routes.

The correctness claim is the stronger one, and it is not statistical: all 30 completed queries returned checksums byte-identical to the recorded 0.1.70 baseline.

What the shape of these results says

Seven queries never read the data. Q01, Q03, Q13, Q14, Q20, Q21 and Q22 return in 0.1–0.6 ms against millions of logical rows. Those are not throughput records.

What serves each of them

Q13 and Q14 — a COUNT(*) and a single-key GROUP BY — answer from cube headers. Q01 takes the PK point-lookup path (121.8 ms on 0.1.86, sub-ms now). Q03 walks the graph index. Q20, Q21 and Q22 are all served by the PTI … INCLUDE (SUM(…), COUNT(*)) grouped mirror declared on orders — Q22 directly on its integer order_ym range, and Q20/Q21 since 0.1.94 via temporal-filter inference, which rewrites a complete-month order_ts BETWEEN to that same order_ym range. The advice stands in its corrected form: what makes these fast is the declared mirror, not the column’s type — Q23 uses the same integer range on the same table without mirror coverage and takes 1,591 ms.

The scan-bound class runs at 8–33 M rows/s. Grouped aggregates, window functions and the lighter joins over millions of rows land here — roughly 4× the 3–9 M rows/s band this page reported on 0.1.86, which is the morsel-parallel scan and parallel aggregation fold doing their work.

Un-mirrored range work is the slowest non-pruned tier. With Q20/Q21 now mirror-served, Q23 (range + IN + LIKE across a join, 2.2 M rows/s) is what remains of it, together with the heavy join tier. Cube skipping still cannot help on this dataset for a reason that is a property of the data: the generator gives every order an independent random timestamp across the year, so all cubes span January to December and no time or value range can exclude one. A time-clustered load would change that; measuring it is open work.

The slowest queries are slow for known, different reasons. Q19 (conditional aggregation, 4.5 M rows/s) spends its time in per-row accumulator updates in the aggregation fold — not in evaluating the CASE, which is vectorized. Q29 (NTILE(10), 1.5 M rows/s — the worst per-row throughput in the suite) materialises and sorts a single partition. And the four-table join tier (Q08/Q09, ~7 M rows/s) is layout-sensitive within a ~2× band, as the caution above documents. All three items are open.

The query that used to time out

Every version of this page before 0.1.94 reported that Q11 did not complete and promised a measured number once the work landed. That promise was kept at 0.1.94 (2,051.6 ms warm), and on 0.1.105 it runs in 408 ms warm (404 ms cold) — now the slowest query in the suite rather than one that timed out.

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;

This is a market-basket self-join: every pair of products appearing in the same order. It is in the suite deliberately as the brutal case — the intermediate result is quadratic in items-per-order. On 0.1.83–0.1.87 it ran past a 120 s timeout and, worse, its runaway poisoned whatever ran after it; the suite quarantines it to run last and alone for that reason, and still does.

Two honesty notes. First, the attribution is not counter-verified: it first completed on 0.1.89 during the ingest-geometry work, and the responsible change has not been isolated with engine counters the way the 0.1.86 join-routing gain was. Second, Q11 has no recorded baseline checksum — no earlier run ever produced a result set to record. Its output (20 rows) is stable run-to-run and its checksum is now recorded as the baseline for future pages.

The queries

Every statement, exactly as executed.

Point lookup / find-one-item

-- Q01  PK lookup on customers
SELECT customer_id, email, country, tier, lifetime_value_cents
  FROM customers WHERE customer_id = 166666;

-- Q02  Secondary-key lookup: all orders for one customer
SELECT order_id, order_ts, status, total_cents
  FROM orders WHERE customer_id = 250007 ORDER BY order_id;

-- Q03  One item for a customer — 4-table hop, latest first
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 = 250007
 ORDER BY o.order_ts DESC, oi.order_item_id ASC
 LIMIT 1;

-- Q04  Same needle via denormalized order_items.customer_id (1 hop)
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 = 250007
 ORDER BY oi.order_item_id DESC
 LIMIT 1;

-- Q05  Full-scan filter on UNINDEXED email
SELECT customer_id, email, tier
  FROM customers WHERE email = 'user166666@mail20.example';

-- Q05b COUNT over an unindexed LIKE prefix scan
SELECT COUNT(*) FROM customers WHERE email LIKE 'user123%';

Q03 vs Q04 is the interesting pair: the 4-table hop (Q03, 0.3 ms) is faster than the single-hop denormalized equivalent (Q04, 71.8 ms), because the graph index serves the join chain while Q04 scans order_items on a non-indexed column.

Joins

-- Q06  2-table join: orders ⋈ customers, count by tier
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: revenue by country
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
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 — predicate pushdown
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
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;

-- Q12  3-table join + LIMIT 100
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;

Q09 is worth comparing against Q08: the same four-table star, but a selective filter cuts it from 2,494 ms to 1,602 ms — predicate pushdown reaching the scan.

Aggregation and GROUP BY

-- Q13  COUNT(*) over the largest table
SELECT COUNT(*) FROM order_items;

-- Q14  Single-key GROUP BY status (6 buckets)
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
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
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
SELECT order_ym, SUM(total_cents) AS revenue, COUNT(*) AS n
  FROM orders GROUP BY order_ym
 HAVING SUM(total_cents) > 2811000000 ORDER BY order_ym;

-- Q18  COUNT(DISTINCT customer_id) per month
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
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;

Range and BETWEEN

-- Q20  Timestamp BETWEEN — one-month slice
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
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 the materialized order_ym
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
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 over all orders
SELECT order_id, customer_id, order_ts, total_cents
  FROM orders ORDER BY total_cents DESC, order_id ASC LIMIT 100;

Q20/Q21 against Q22 is the widest gap on this page — 0.1 ms versus ~1,100 ms for logically similar range slices — but see the correction above for what actually decides it. It is not the integer column: Q23 ranges over the same integer order_ym and takes 1,151 ms. Q22 is answered from the PTI … INCLUDE grouped mirror declared on orders, so the aggregate is pre-computed. The actionable lesson is declare a mirror for the aggregate you run often, not change the column type.

Window functions

-- Q25  ROW_NUMBER per customer → each customer's latest order
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 — top 3 products by revenue per category
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
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
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
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
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;

How to read these numbers

1. Single client, single run. No concurrency, no variance bars. Warm figures are a median of three; cold is one observation.
2. The daemon was dedicated and idle — and that matters. Earlier versions of this page ran against the host’s shared daemon and flagged unquantified background load; this run isolates it. The measured cost of NOT doing so: the join tier ran 2–3× slower on the shared daemon with identical code and data. If you benchmark on a daemon co-hosting other databases, expect that penalty.
3. These are not TPC-H or ClickBench results. No standard industry benchmark has been run against AetheriusDB, and there is no published head-to-head against another engine.
4. Warm means warm. The dataset fits in memory on this host. Nothing here characterises behaviour when the working set exceeds RAM.
5. All 31 queries completed for the first time on this page; totals include Q11 where stated. The 30-query subtotal is kept alongside so the number remains comparable with earlier versions.
6. This suite is read-only. It never writes, so nothing here says anything about write cost. That is measured separately in the UPDATE Write Benchmark, and the answer is materially different in shape from the read results on this page.

UPDATE Write Benchmark →

The write side — what an UPDATE costs, which this suite never measures.

Benchmarks (earlier runs) →

Ingest throughput and the earlier 500 K-row suite.

Advanced Indexing (PTI) →

Answering grouped aggregates without a table scan.

Graph Index →

The join-chain path behind Q03's 0.3 ms.

Morsel Parallelism →

The parallel execution model behind these timings.