pg_mentat 1.10.0: AVET, count, auto-index, edn_q_rows – results

c7i.8xlarge (32 vCPU Xeon 8488C, 61.8 GB), Amazon Linux 2023, PostgreSQL 16.15 release build (shared_buffers 16GB, work_mem 64MB, max_parallel_workers_per_gather 4), benchmarks/scale with PHASE=bench SCALES="s m" BACKENDS=pg EXTRA=concurrency_sweep CLIENTS="1 8 32" REPS=3. s = 984,803 datoms (1,600 users, 130,000 issues), m = 9,850,258 (16,000 users, 1.3M issues).

Three runs of the same suite over the same datasets:

  • v1.9.0 – ~/results/before-190 (the 1.9.0 install);
  • after M1 – ~/results/after-m1 (3a1d1cd8, AVET indexes);
  • final – scale/ here (46b47573 plus the auto-index skip rule), all 36 correctness checks pass.

compare.txt has benchmarks/scale/compare.py for final against both baselines (p50, p99, throughput): 0 regressions at 10% against either.

Summary (p50 ms at 1 client; concurrency_sweep in ops/s)

scenario scale clients v1.9.0 after M1 final unit
aggregate (q3) m 1 1427.821 1445.204 57.659 p50 ms
aggregate (q3) s 1 111.918 34.551 9.172 p50 ms
as_of m 1 2.022 2.032 1.962 p50 ms
as_of s 1 1.713 1.653 1.643 p50 ms
concurrency_sweep m 1 570.600 1204.900 1262.500 ops/s
concurrency_sweep m 8 4401.200 9508.700 9067.000 ops/s
concurrency_sweep m 32 12524.000 28538.600 30718.200 ops/s
concurrency_sweep s 1 1176.000 1297.200 1277.600 ops/s
concurrency_sweep s 8 9599.500 10379.800 10234.700 ops/s
concurrency_sweep s 32 27560.900 31113.900 32569.000 ops/s
input_bindings m 1 2.697 0.926 0.922 p50 ms
input_bindings s 1 1.078 0.834 0.814 p50 ms
point_lookup (q1) m 1 1.654 0.190 0.191 p50 ms
point_lookup (q1) s 1 0.425 0.185 0.191 p50 ms
predicate_scan (q4) m 1 764.220 619.823 612.583 p50 ms
predicate_scan (q4) s 1 79.116 71.149 48.908 p50 ms
pull m 1 0.845 0.875 0.876 p50 ms
pull s 1 0.824 0.854 0.845 p50 ms
ref_traversal (q2) m 1 2.542 1.164 1.131 p50 ms
ref_traversal (q2) s 1 1.104 0.955 0.915 p50 ms
since m 1 3.098 3.171 3.047 p50 ms
since s 1 1.920 1.915 1.890 p50 ms

M2: count (q3 [:find ?state (count ?i) :where [?i :issue/state ?state]])

1445 ms -> 58 ms at 10M datoms (25x), 35 -> 9 ms at 1M. explain/before-190-m-q3.txt vs explain/final-m-q3.txt:

  • before: GroupAggregate over a Sort of all 1.3M (value, e) pairs (external merge, Disk: 45296kB) for count(DISTINCT e);
  • after: (?state, ?i) is a key of the join (?i is the entity of a cardinality-one pattern), so COUNT(*), grouped on the raw keyword: Finalize GroupAggregate <- Gather Merge (3 workers) <- Partial HashAggregate <- Parallel Seq Scan current_keyword. At 1M the planner instead streams idx_current_keyword_avet in value order into a GroupAggregate.

Parts of the gain (m): COUNT(*) instead of COUNT(DISTINCT) 1491 -> 214 ms (serial); aggregate queries skipping the SPI plan cache so they can run parallel (SPI-prepared plans never are) 214 -> 65 ms; grouping on the raw typed column instead of the decoded text 65 -> 55 ms.

A non-key aggregate ([:find ?state (count ?u) :where [?i :issue/state ?state] [?i :issue/assignee ?u]], distinct assignees per state) takes the de-duplicating path SELECT DISTINCT -> HashAggregate: 406 ms at m (m2-count/m-nk.*), against 762 ms for the old COUNT(DISTINCT) form of the same query (probe in psql).

M3: automatic index management

explain/autoindex-*.txt: a synthetic store (8 long attributes x 200k entities, so each is 1/8 of datoms_long_new), as-of range queries on one attribute, then mentat_tune_indexes(false):

  • wide range (25% of the values): the planner scanned all of the attribute’s values through idx_datoms_long_new_aevt with Filter: v >= ...; after, Parallel Index Scan using mentat_auto_datoms_long_new_a1000004. edn_q p50 162 -> 141 ms (-13%).
  • narrow range (0.5%): the VAET index already served it; 2.8 -> 2.6 ms.
  • the scale suite’s :issue/priority (100% of datoms_long_new): 655 -> 606 ms for a 62 MB index. This led to the skip rule: an attribute holding at least half its table is reported skip, not indexed.

So the manager is a modest, targeted win for hot temporal range queries. The large wins in this release are M1 (AVET) and M2 (count).

Regression caught and fixed (46b47573)

Between after-m1 and the first final run, input_bindings at s went 0.834 -> 1.131 ms. A/B of the release .so of each commit on the same DB (pgbench, 100-email [?email ...] input, -c1 -T10 x2): 3a1d1cd8 0.89, bd14ee6d 0.88, c19fe955 1.18, HEAD 1.18. The literal a = <entid> pushdown (c19fe955) changed edn_q’s plan-cache choice for a statement with one parameter per collection value. Fix: bind the collection as one array (v = ANY($n)) -> 0.87 ms; final run 0.814 ms.

Files

  • scale/ – the final run (timings.csv, raw.csv, summary.md, plans/, logs/, env.txt, checks.txt);
  • compare.txt – compare.py vs v1.9.0 and vs after-M1;
  • explain/ – q1/q3/q4 plans for v1.9.0, after-M1 and final, and the auto-index before/after plans;
  • m2-count/ – the M2 q3 / non-key / :with / many-attr probes (SQL, plan, result, timings) at s and m.