/tuning-oak-query-indexes

name: tuning-oak-query-indexes
description: Use when a Jackrabbit Oak/AEM JCR query is slow, logs a traversal warning, or you're adding/changing a query and need to check or update its property/Lucene index definition so the query is answered by the index instead of in-memory filtering or sorting.
license: Apache-2.0
compatibility: Any Jackrabbit Oak-backed repository on AEM 6.5 LTS/AMS using property or Lucene query indexes.

Tuning Oak Query Indexes

Overview

An Oak query only uses an index for the parts the index actually covers. Any `WHERE`/`ORDER BY`/fulltext field without a matching, correctly-flagged property definition gets evaluated node-by-node in memory — same for a wrong nodetype or path scope, **even if every field is indexed**. This skill is the procedure for finding exactly which fields/conditions aren't covered and what index-definition change fixes each one.

**Ground truth for every claim below**: [the AI agent indexing guide](references/ai-agent-indexing-guide.md), bundled alongside this skill so it works the same way regardless of which codebase it's copied into. Read it on demand for exact property tables, defaults, cost-model details, and edge cases — this skill is the checklist, not the reference.

**This skill assumes nothing about the codebase it's used in beyond "you have a query and an index definition."** It does not require an AEM instance, a running Oak repository, or a checkout of Oak's own Java source — those only *strengthen* confidence where noted (step 6 and the "explain can't reach this" fallback below), never a requirement; the core procedure — read the query, read the index definition, find the gap, propose the fix — works from the two artifacts alone. Index definitions in a real project are often FileVault XML (`.content.xml` under an `/apps/.../install` content package, `jcr:primaryType="oak:QueryIndexDefinition"` and friends), not raw JSON — read them the same way; the property names and semantics are identical, only the serialization differs.

Procedure

**Required input — the index definition(s). This skill does not run without them.** The whole assessment is a comparison of a query against the index definition(s) it could use, so the index definition is mandatory input, not an optional aid. If it was not provided, **ask for it** — accept any of: a FileVault `.content.xml` under an `/apps/.../install` package, a JSON export, or the live dump at `GET /system/console/status-oak-index-defn.json` (admin auth). **If none can be obtained, skip this skill and say so explicitly** — do not emit a coverage assessment from the query alone. A guess about what is indexed reads as a finding and gets acted on as one; no answer is better than a wrong one. (The query is the other required input — provide it directly, or build the inventory with the bundled [extracting JCR queries guide](references/extracting-jcr-queries.md).)

**Preconditions**: steps 1 and 6 below both mention live-instance tools (`explain`, the Felix `InventoryPrinter`, Oak's trunk test suite) — neither is required. Given the query text and the index definition(s), skip those tools' sub-bullets, do steps 2-5 and 7 as pure static analysis, and report per the final section with verification stated as reasoned-but-unproven.

1. **Get the query and every index definition it could plausibly use** — not just the one you assume applies. **If you don't already have the query in hand** — e.g. asked to audit a whole app/bundle rather than tune one query someone handed you — first follow the bundled [extracting JCR queries guide](references/extracting-jcr-queries.md) to find every JCR/QueryBuilder query the codebase actually issues (including ones assembled programmatically through wrapper/helper/DAO code, which a text grep alone misses); its output (construction site + every trigger/caller path) is the input to this skill's assessment, one query at a time. **If no live instance is reachable, work from every index definition you were actually given** and say so in the report (§ Report shape) — candidate selection is then reasoned, not confirmed. If a live instance *is* reachable, `explain` tells you which index actually gets picked, which is stronger than reasoning from the definitions alone; to enumerate every index definition in the repository (including non-root ones a per-path lookup would miss), use the Felix `InventoryPrinter` status page `oak-core` itself registers — it works on any Sling/Felix-based Oak deployment, not just AEM: `GET /system/console/status-oak-index-defn.json` (admin auth). It dumps every index definition (config only, no hidden `:data`/`:index` storage nodes) in the same `nam:`/`str:`/`dat:`/... type-prefixed JSON encoding oak-run's `--index-definitions-file` uses — one request beats guessing candidate index names one at a time. 2. **Segregate every query into two sections before analyzing anything** — Section A (static fields) and Section B (dynamic fields):

• **Section A — static fields**: the property *path/name* the query filters, orders, or searches on is

fixed in source — a literal string or a named constant — even if the *value* compared against it varies at runtime (a caller-supplied ID, a loop variable, a request parameter used only as a value). A query stays in Section A as long as you can point at the exact `WHERE`/`ORDER BY` field name without guessing.

• **Section B — dynamic fields**: the property *path/name itself* is not fixed in source — it comes from

OSGi config (`detectMetadataField`-style properties), a request/predicate-map key, a content-authored query (`dam:query` nodes, Smart Collections), or a script/native query string. You cannot state which property the index needs to cover without external input. **Assess Section A fully — that's the primary deliverable.** For Section B, do **not** guess the likely or default field name and assess that g