Tips for writing queries

The engine has no cost-based optimizer: plans are deterministic, and the same query shape always compiles to the same plan. That cuts both ways. You never have to out-guess a planner, but a slow query stays slow until you change it, and the fixes are habits rather than hints. This page collects those habits: what mnestic now fixes automatically, what still needs care, and the patterns that keep queries fast as relations grow. The machinery behind every claim here is described in Query execution.

The examples run against the agent-memory graph used throughout these docs. Keys sit to the left of =>:

memory   { id => kind, text, importance, at, v }   # notes, decisions, insights
entity   { id => name, kind }                      # people, tools, projects
mentions { memory, entity }                        # which memories involve whom
recalls  { from, to => strength }                  # association edges

Check the plan, not the clock

When a query feels slow, wrap it in ::explain { ... } before reaching for a stopwatch: because plans are deterministic, the plan names the problem. Two join operators carry most of the signal.

• stored_prefix_join — the bound columns form a prefix of the relation's (or an index's) key, so each probe is a keyed lookup: logarithmic in the relation size, and what you want to see on large relations.
• stored_mat_join — the bound columns do not form a key prefix, so the relation is scanned and matched row by row.

A third pattern, the (cartesian) annotation, marks a join that shares no bound variable with its left input; it usually means a typo (shown below). For the column-by-column guide to plan output, see Reading a plan with ::explain; plans on this page are trimmed to the interesting columns.

Filters on key columns become lookups

There are two ways to pin a key column to a value: bind first (id = 'm4', *memory{ id, .. }) or filter after (*memory{ id, .. }, id == 'm4'). In mnestic they compile to the same keyed plan:

::explain { ?[kind, text] := *memory{ id, kind, text }, id == 'm4' }

atom_idx  op                  ref       joins_on        filters/expr
3         unify               id                        "m4"
2         load_stored         :memory
1         stored_prefix_join            {"id": "**0"}
0         out

The equality was hoisted into a unify, and the access is a stored_prefix_join — a point lookup on memory's key. Writing id = 'm4', *memory{ id, kind, text } produces the byte-identical plan, and the positional form *memory[id, kind, text, _, _, _], id == 'm4' is converted the same way.

Two shapes still scan, by design. A non-equality predicate on a key column is a filter, not a lookup — the whole relation streams through it:

::explain { ?[id] := *memory{ id }, id > 'm5' }

atom_idx  op            ref        joins_on   filters/expr
1         load_stored   :memory               ["gt(id, \"m5\")"]
0         out

And a numeric equality keeps its post-filter semantics even on a key column, because == compares across Int/Float (1 == 1.0 is true) while key order distinguishes them — converting could silently drop cross-type matches, so the pushdown declines. On a relation event { seq: Int => label }:

::explain { ?[label] := *event{ seq, label }, seq == 7 }

atom_idx  op            ref       joins_on   filters/expr
1         load_stored   :event               ["eq(seq, 7)"]
0         out

For numeric keys, write the binding first — when you know the value has the column's type; the binding-first form matches structurally, so an Int key never matches a Float value. That form is a keyed lookup:

::explain { ?[label] := seq = 7, *event{ seq, label } }

atom_idx  op                  ref      joins_on         filters/expr
3         unify               seq                       7
2         load_stored         :event
1         stored_prefix_join           {"seq": "**0"}
0         out

Non-key columns need an index

A filter on a value column has no key to push into — matching is prefix-only, on the relation's key or an index's key. Create an index whose key starts with the filtered column and the same query compiles to a keyed lookup, no rewrite required:

::index create memory:by_kind { kind, id }

::explain { ?[id] := *memory{ id, kind: 'decision' } }

atom_idx  op                  ref               joins_on        filters/expr
3         unify               *5                                "decision"
2         load_stored         :memory:by_kind
1         stored_prefix_join                    {"*5": "**0"}
0         out

When the rule also needs columns the index does not carry, the engine selects on the index and joins back to the base relation by primary key — both joins keyed:

::explain { ?[id, text] := *memory{ id, text, kind: 'decision' } }

atom_idx  op                  ref               joins_on        filters/expr
5         unify               *4                                "decision"
4         load_stored         :memory:by_kind
3         stored_prefix_join                    {"*4": "**1"}
2         load_stored         :memory
1         stored_prefix_join                    {"**2": "id"}
0         out

An index is chosen only when a prefix of its key is bound — the engine never gambles on a bad index, and you can always query the index directly. See Indices.

Atom order and the greedy reorder

Upstream, join order was exactly the order you wrote the atoms in, and a conjunction listed in schema order rather than join order could spin on an enormous intermediate. Since 0.10.5, mnestic reorders the positive stored-relation atoms of a conjunction with a deterministic, stat-free heuristic: fewest new variables first, preferring an atom whose entire key is bound (a point lookup) on ties, falling back to written position. On any written order the greedy heuristic would itself choose, the pass is the identity — such hand-tuned queries compile byte-identically; an order that deliberately disagrees with it can be pinned with :reorder written.

Know what it does not do:

• It consults no statistics — no cardinalities, no cost model. It removes one structural pathology (a disconnected or badly interleaved join order). When two orders are structurally equivalent but one touches far more rows, the heuristic cannot see that; you are still the optimizer, and the working rule is unchanged from upstream: put the most restrictive atoms that introduce new bindings first.
• It declines rules with fewer than three stored-relation atoms, rules containing derived-rule or index-search (HNSW/FTS/LSH) atoms, and rules with a multi-valued in unification.
• A bare :limit without :order compiles the written order, so the returned subset stays the one your written order would produce.
• :reorder written opts a query out entirely.

One ordering problem no reorder can fix is a conjunction that is genuinely disconnected — and the usual cause is a misspelled variable. Here the second atom was meant to say from: m:

::explain {
    ?[entity, next] := *mentions{ memory: m, entity },
                       *recalls{ from: mm, to: next }
}

atom_idx  op                               ref        joins_on
3         load_stored                      :mentions
2         load_stored                      :recalls
1         stored_prefix_join (cartesian)              {}
0         out

joins_on is empty and the operator is annotated (cartesian): every mentions row pairs with every recalls row. A Cartesian product you did not intend is worth grepping your variable names for.

Break long join chains into smaller rules

Say you want the memories exactly four association hops downstream of m1. One way:

?[who] := *recalls{ from: 'm1', to: f1 },
          *recalls{ from: f1, to: f2 },
          *recalls{ from: f2, to: f3 },
          *recalls{ from: f3, to: who }

Another:

h1[who] := *recalls{ from: 'm1', to: who }
h2[who] := h1[m], *recalls{ from: m, to: who }
h3[who] := h2[m], *recalls{ from: m, to: who }
?[who]  := h3[m], *recalls{ from: m, to: who }

Both return the same rows (here, m5). But on a dense graph, where many paths lead to the same node, the second form is exponentially faster: relations obey set semantics, so each hN deduplicates its frontier before the next hop, while the single rule enumerates every path to the final layer. Note that this is not an atom-ordering problem — the chain's written order is already the greedy one, so the reorder leaves it untouched; only rule boundaries deduplicate. Rules in the same stratum are also evaluated in parallel where the platform allows.

The moral: prefer breaking a query into smaller rules. It reads better, and in this engine it almost always runs faster too. And when the query is genuinely recursive, make it a recursive rule:

f_n[who, min(layer)] := *recalls{ from: 'm1', to: who }, layer = 1
f_n[who, min(layer)] := f_n[m, last], *recalls{ from: m, to: who },
                        layer = last + 1, layer <= 4
 
?[who, layer] := f_n[who, layer]
:order layer, who

who    layer
m2     1
m3     2
m5     2
m4     3

Two things to keep straight about this pattern:

• It computes minimum hop distance, not "exactly N hops". Filtering with ?[who] := f_n[who, 4] returns nothing on this graph: everything four hops out is also reachable sooner (m5 sits at minimum layer 2, via the shortcut edge m2 → m5). Only the chain forms above answer the exactly-N-hops question.
• The layer <= 4 guard caps the work, not the termination. Because min is a semi-lattice aggregation, this recursion reaches a fixpoint even on cyclic graphs without the guard — a longer route to an already-reached node changes nothing. Drop the aggregation, though, and the same shape carries ever-growing layer values around any cycle forever; then the guard (or a :timeout) is what saves you.

When one big rule is right

The single-rule chain has two legitimate uses. First, with :limit 1 it stops at the first path found (early stopping), and skips the bookkeeping of extra rules:

?[who] := *recalls{ from: 'm1', to: f1 },
          *recalls{ from: f1, to: f2 },
          *recalls{ from: f2, to: f3 },
          *recalls{ from: f3, to: who }
:limit 1

Early stopping works when the entry rule is an inline rule and there is no :order; with a sort, all rows must exist before the limit applies. See Early stopping.

Second, counting paths requires the single rule, because set semantics in a smaller rule collapses the duplicates you are trying to count. Two-hop paths through the association graph:

?[count(c)] := *recalls{ from: a, to: b }, *recalls{ from: b, to: c }

count(c)
6

Route the endpoint through an intermediate rule and the count silently becomes "distinct endpoints" instead:

hop2[c] := *recalls{ from: a, to: b }, *recalls{ from: b, to: c }
?[count(c)] := hop2[c]

count(c)
4

The single-rule count is safe on memory even at scale: rows stream through the counter as the join produces them, and no intermediate path relation is materialized.

Parameterize queries

Pass runtime values as parameters instead of splicing them into the query text. Parameters are named with a $ prefix and supplied alongside the script by every binding (run_script in Rust and Python, the params field over HTTP):

?[text] := *memory{ id: $id, text }

With {"id": "m3"}:

text
Nightly compaction stalls search-service around 03:00

A parameter reaches the plan as a bound constant, so the keyed lookup is identical to writing the literal:

::explain { ?[text] := *memory{ id: $id, text } }

atom_idx  op                  ref       joins_on        filters/expr
3         unify               *5                        "m3"
2         load_stored         :memory
1         stored_prefix_join            {"*5": "**0"}
0         out

String interpolation, by contrast, is an injection surface (a value containing a quote is now part of your query) and an escaping bug waiting to happen. This matters double when the values come from an LLM.

Stage shared work in ephemeral relations

When several queries need the same intermediate result, compute it once into an ephemeral relation (name starting with _) inside a chained transaction, then reuse it. Here the set of high-importance memories is staged once and then self-joined:

{ :create _hot { id: String } }
{
    ?[id] := *memory{ id, importance }, importance >= 0.75
    :put _hot { id }
}
{
    ?[a, b] := *_hot[a], *_hot[b], *recalls{ from: a, to: b }
    :order a
}

a     b
m2    m5
m4    m5
m8    m5

Ephemeral relations live exactly as long as their transaction: a standalone :create _draft { .. } script succeeds and the relation is gone before the next script runs. The chaining mini-language (%loop, %if, %swap, …) where ephemeral relations do their real work is covered in Chaining queries.

Give every unbounded query a budget

Explicit unification makes it possible to write queries with infinite results, and no compiler can reject them all without also rejecting valid queries:

count_up[n] := n = 1
count_up[n] := count_up[m], n = m + 1
?[n] := count_up[n]
:timeout 1

eval::timeout
 
  × Query exceeded its time budget
  help: The query ran past its wall-clock budget, set by a `:timeout`
        option, a per-call timeout, or the Db default query timeout.
        Narrow the query or raise the budget.

Make the budget a habit for any query whose termination depends on data you do not control — recursion over user-supplied graphs, exploratory joins, and especially queries authored by an LLM. A runaway query can also be stopped from outside with ::kill (see System ops).

Dealing with nulls

The engine is strict about types: comparisons are only defined between values of the same type. Given confidence { memory => score: Float? } where some scores are null, this query throws as soon as it meets one:

?[memory] := *confidence[memory, score], score > 0.5

eval::throw
 
  × Evaluation of expression failed
  help: comparison can only be done between the same datatypes, got null
        and 0.5

One solution is to treat null as equivalent to a default value with ~, the coalesce operator:

?[memory] := *confidence[memory, score], (score ~ -1) > 0.5

memory
m2

The parentheses are not necessary — ~ binds tighter than the comparison — but it reads better this way. You can also check for null explicitly:

?[memory] := *confidence[memory, score], if(is_null(score), false, score > 0.5)

cond is also helpful in this case; see Functions for coalesce, if and cond.