/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

**Preconditions**: steps 1 and 6 below both mention live-instance tools (`explain`, the Felix `InventoryPrinter`, Oak's trunk test suite) — neither is required. If you only have 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 — use the `extracting-jcr-queries` skill first 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 guess as if it were the query's real target. Instead, list each Section B query with the specific missing input (e.g. "OSGi config value of `detectMetadataField`", "the `type` request parameter passed to this servlet") and ask the user for it. Only fold a Section B query into a real assessment once the user supplies the concrete field name(s) it actually uses in their deployment — treat that answer the same as a Section A fact from that point on.

| Excuse to assess anyway | Reality | |---|---| | "The default value is probably X, I'll note the caveat" | A caveated guess still reads as an assessment and gets acted on as one — if the deployment overrides the config, the whole coverage table is wrong. State the missing input and stop instead of assessing the default. | | "Partial assessment is still useful" | Partial-but-wrong is worse than no assessment — it's indistinguishable fro