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,
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.)
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.
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 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.
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 from a verified finding in the report. Segregate it into Section B and ask. |
| "I already covered the default case in Technique 1" |
Knowing the default is not the same as knowing the configured value. Confirm before assessing, don't infer. |
| "Asking breaks the flow of the report" |
The report is still complete — Section A stands on its own. Section B becomes assessable the moment the user answers. |
Extract every field/construct the query touches: WHERE equality/range/IN/IS NULL/
IS NOT NULL, ORDER BY columns, contains(...)/fulltext, rep:facet/rep:similar/rep:suggest/
rep:spellcheck, path restriction (ISDESCENDANTNODE///), and the query's own nodetype restriction
(FROM [type] / XPath element(*, type)).
- AEM QueryBuilder predicate maps — the field name is usually a value, not a key. For the
property, relativedaterange, daterange, boolproperty, and similar predicate types, the map
key (1_property, 5_relativedaterange.property, ...) is just the predicate-type token plus its
group index — it names which predicate, not which JCR property. The actual field the index needs
to cover is the string value assigned to that key, e.g. map.put("1_property", "jcr:content/hasValidMetadata") → the field to check index coverage for is
jcr:content/hasValidMetadata, read from the value, not from the literal text 1_property. Same
pattern for map.put("1_relativedaterange.property", "jcr:created") → field is jcr:created. Don't
let this trip up the Section A/B call in step 2: the field name here is still a hardcoded string
literal in source (Section A), even though it doesn't look like a typical WHERE-clause field the way
a SQL2/XPath condition does — it only becomes Section B if that value itself is a variable/request
parameter rather than a literal.
For each field, look up the required flag (table below) and check the index has a property
definition with a matching name carrying it.
Check the two scoping gotchas before anything else — these skip the index entirely regardless of
how well the fields are indexed, and are the most common reason an apparently-correct index isn't
picked:
- Nodetype scoping: does the query's own
FROM/element() restriction name the index's declared
type (or a subtype)? If the query is unrestricted (bare * / implicit nt:base) but the index only
declares rules for one type (declaringNodeTypes on a property index, or indexRules not including
nt:base on Lucene), the index is invisible to the planner — cost Infinity, or absent from
the candidate list entirely. Fix by adding the nodetype restriction to the query, not by changing the
index.
- Path scoping (Lucene only): do
includedPaths and queryPaths match each other, and
does the query's actual path restriction fall inside them? Leaving queryPaths at its default (/)
while narrowing only includedPaths is worse than not being selected — the index gets picked outside
its real coverage and silently returns fewer results than actually exist.
Verify empirically, don't just reason from the spec. Run explain <query> (or the AEM Query
Performance tool). Confirm: (a) the expected index appears in the candidate/cost log at all, (b) its
cost beats every alternative including traversal, (c) the plan shows no traverse and no unindexed
residual condition for a field you believe is covered. A finite cost does not by itself prove a given
condition is served natively — live-confirmed: an index selected at a normal-looking finite cost
(beating traversal, due to nodetype/path scoping alone) had a plan whose actual luceneQuery was a bare
*:*, meaning an equality condition on an unindexed property was still being evaluated as a residual
in-memory filter, not pushed into the index at all. Always read the logged luceneQuery/plan text
itself for the specific field you care about, not just whether some index got a finite cost.
explain only tells you which index and cost/plan get picked — it says nothing about whether a
feature that doesn't work through the cost-plan mechanism actually behaves correctly: suggestions
(rep:suggest), spellcheck (rep:spellcheck), facet counts, similarity ranking (rep:similar),
and rep:native all fall in this category and need their own verification, not an explain check.
See the indexing guide's "Verifying features explain can't reach" (Appendix B) for the two
ways to get real signal (a live-instance config/MBean cross-check, or the exact Oak trunk unit tests to
run) — and if neither is available, say so plainly in the report rather than guessing.
Identify the single existing index the fix belongs on before writing any change. Never invent a
brand-new index or silently pick one of several plausible candidates — the target index changes the
whole recommendation (which properties it already has, its current tags/weight/reindex history), so
getting it wrong makes step 8's "corrected index definition" wrong too.
- Live instance reachable: run
explain on the query as-is (even though you expect a gap — the
candidate list and per-index cost the response returns is exactly what's needed here, not just a
pass/fail). explain's response enumerates every candidate index it considered with its cost, not
just the winner — read that list, not just the top line. The lowest-cost index that also matches the
nodetype/path scoping from step 5 is the target. State the cost numbers you read, not just the index
name, so the choice is checkable.
- No live instance: enumerate every index definition you were given whose nodetype+path scoping
(step 5) matches the query — these are the only real candidates regardless of how many index files
exist in the repo. Then:
- Exactly one candidate matches → that's the target, proceed.
- Zero candidates match → there is no existing index to extend; say so explicitly and propose a
new index only as a last resort (this is the one case where inventing an index is correct, and it
still needs to be called out as such, not presented as "the fix" silently).
- More than one candidate matches (contending indexes) → do not silently pick one — this is
exactly the "a property IS indexed, but a different, cheaper index still wins" mistake from the
list above, except now you're the one choosing without the cost data
explain would have given you.
Present the contenders side by side — for each: which of the query's fields it already covers,
its tags, weight (if set), includedPaths/queryPaths, and how narrowly it's already scoped —
and ask the user which index they want the change applied to. Do not propose a corrected definition
for any of them until the user picks one.
Propose the smallest fix that gives every field its required flag. Prefer one cohesive index per
nodetype over several overlapping single-purpose ones. Set reindex: true for the change, not
refresh: true, unless you can positively confirm no existing content already carries the
new/changed property's value — refresh: true only makes a new property definition plannable
(explain will show it as a candidate); it does not backfill pre-existing documents that already had
the value before the definition existed. Live-confirmed: a property added with refresh: true alone
showed up in explain immediately, but a real query for a pre-existing document's value returned zero
results until a genuine reindex: true ran. If you can't be certain the property is genuinely new to
every document, reindex. Before changing the index, check whether tightening the query
gets you further for less cost — a more constrained query is always cheaper and safer than a broader
index, in this order of preference:
- Add/narrow a nodetype restriction (
element(*, T) / FROM [T]) — the single highest-leverage change;
see the nodetype-scoping gotcha above. Never use a bare */nt:base restriction if the real target
type is known.
- Add/narrow a path restriction (
ISDESCENDANTNODE/// scoped as tightly as the use case allows) —
shrinks both the traversal fallback cost and, if the index is path-scoped, keeps it eligible.
- Add/tighten property conditions (more selective equality/range conditions push more work into the
index and reduce what's read out of it).
Only after the query is as constrained as the use case genuinely allows should the index definition
itself be widened — widening the index is the more expensive, more permanent change (more storage, more
indexing time, affects every other query that hits it), while tightening the query text is free and
query-request-local. Producing the corrected index definition (the JSON/content diff) is not the same
as applying it — writing it out for review is fine to do unprompted, but actually pushing it to a live
instance (a Sling POST/
curl -F, a .content.xml commit, or any other write) is a repository-mutating
change like any other and needs the user's explicit go-ahead first, same as the
indexing guide's Appendix B requires for its create/modify/delete commands.
Re-verify with explain after the change has been applied (and after the async cycle, if applicable) —
confirm no residual/in-memory filtering remains, and any ORDER BY is served by the index, not a full
in-memory sort.
Field construct → required index flag
| Query construct |
Property index (type=property) |
Lucene property definition |
Equality / IN |
in propertyNames |
propertyIndex: true |
IS NOT NULL |
in propertyNames (property index has no dedicated existence-only flag) |
propertyIndex: true, or the cheaper notNullCheckEnabled: true alone if you never need equality/range/ordering on the value — see the cost note below |
IS NULL |
n/a — no mechanism exists for type=property |
nullCheckEnabled: true (standalone, propertyIndex not required) |
ORDER BY |
not supported |
ordered: true + explicit type |
contains(prop, ...) |
not supported |
analyzed: true |
contains(., ...) (node-scope) |
not supported |
analyzed: true + nodeScopeIndex: true on every contributing property |
rep:facet |
not supported |
facets: true |
Range (<,>,<=,>=) |
works if in propertyNames |
propertyIndex: true + explicit type |
Relative property (a/b/@c) |
not supported |
name: "a/b/c" |
Function (lower(...), name(), path()) |
not supported |
function: "..." + propertyIndex/ordered as needed |
Always set an explicit type (Date/Long/Double) on any property used in ORDER BY or a range
condition — without it, values can be compared lexicographically (numeric-looking strings sort
"1","10","2"; unparsed dates sort before all valid ones instead of erroring or being excluded).
Index size / storage cost pitfalls
Every fix in this skill makes a query faster by making the index cover more. That's not free — always
surface the tradeoff when a proposed change triggers one of these, don't just add the flag silently:
| Enabling this... |
...costs this |
nodeScopeIndex on any property in a rule |
Indexes the node name for every node matching that rule, not just the boosted property — large for broad rules like nt:base. Scope the rule to the narrowest real nodetype before turning this on. |
evaluatePathRestrictions |
Slight but real per-document storage increase (stores ancestor-path terms). Only enable on indexes that actually need native path-restriction evaluation (§4.2's queryPaths correctness case), not by default. |
ordered on a property |
Increases index size (doc-values field per ordered property). Intended for single-valued properties only — but enabling it on a multi-valued one does not hard-fail: it just logs a WARN and skips the doc-values field for that document, so ORDER BY silently degrades to an unpredictable position for multi-valued documents instead of erroring. Only mark properties ordered that a real ORDER BY actually uses, and don't rely on a hard failure to catch a multi-valued mistake — check your data. |
| Many properties in one broad index "just in case" |
Bigger index, slower indexing, and — per the nodetype/path scoping gotchas above — no selection benefit if the query doesn't ask for them. Prefer narrow, cohesive indexes over one large catch-all. |
| A single Lucene index approaching ~2^31 documents |
Hard ceiling in the Lucene version Oak ships — the index can stop being openable past this. Split into multiple narrower (nodetype- or path-scoped) indexes (Solr used to be an escape hatch for this; it was removed in Oak 1.82). |
Long text values with no maxFieldLength override |
Only the first ~10000 terms per field are indexed by default — a large document field is silently truncated, not indexed in full, which can produce fulltext misses on content past that boundary. |
propertyIndex: true used purely for IS NOT NULL/IS NULL, with no equality/range/ordering ever needed |
Wastes storage — propertyIndex indexes every distinct value as its own term in a dedicated field. notNullCheckEnabled/nullCheckEnabled alone (no propertyIndex needed) do the same existence check via one lightweight, unstored term in a shared field across the whole index (:notNullProps/:nullProps) — genuinely cheaper, live-confirmed to work standalone. The flip side: nullCheckEnabled on a broad/generic nodetype where the property is rarely set can itself create an entry for nearly every document (an entry per node without the value) — scope it to a narrow nodetype. |
The general principle: the more constrained a query is, the cheaper and safer it is to serve — a
query naming a specific, narrow nodetype and path is always preferable to a broad nt:base/unscoped one,
independent of whatever the index does. Recommend query tightening first (step 8) before recommending an
index that has to compensate for an unnecessarily broad query.
Common mistakes (each one live-verified during this skill's development)
Beyond the two scoping gotchas in step 5 (a nodetype-scoped index skipped entirely when the query has no
matching restriction; includedPaths/queryPaths mismatches silently returning fewer results):
sync/unique on a Lucene property definition, but the index's own async is still a single string
(not ["async","sync"]/["async","nrt"]) → silently never synchronous, never enforced.
- A property IS indexed, but a different, cheaper index still wins → check
weight (lower = cheaper,
default 5), and re-check the scoping gotchas above on the index you expected to win.
- Indexing many unrelated properties into one index "just in case" → prefer one cohesive index per
nodetype; overlapping indexes with different
includedPaths/excludedPaths for the same nodetype cause
ambiguous selection.
rep:facet/rep:similar hit the nodetype-scoping gotcha too — rep:facet fails with a hard exception
("... can't be evaluated by traversal") instead of silently falling back like plain fulltext. This
only happens when the query actually runs — explain on the same query does not throw; it returns a
normal success with a traverse/estimatedEntries: Infinity plan, easy to misread as "fine, just slow."
Don't trust explain succeeding as proof a rep:facet query works — run it for real, or treat an
Infinity-cost traverse plan on a facet query as an early-warning sign it will fail at execution.
- A raw
rep:native('idx', 'field:value') query string silently matches nothing if field is an
analyzed property — its real Lucene field name is full:field (escape the colon:
full\:field:value), not the bare JCR property name. Only non-analyzed (propertyIndex-only) fields
use the bare name. Root-caused via FieldNames.ANALYZED_FIELD_PREFIX and live-confirmed side by side
(unescaped form: 0 results; full\:-escaped form: correct match, same index/content) — this is the
single most common reason a native query "doesn't work" despite a correct-looking explain plan.
Report shape
Structure the report in the two sections from step 2 — never merge them:
Section A — static fields (assess fully)
For every Section A query, produce:
- A table: field/construct → covered? → what's missing.
- Root cause per uncovered field (which failure mode from the table/mistakes above — including the two
scoping checks, even when they pass).
- Whether the query itself can be more constrained first (nodetype, then path, then property conditions —
see step 8) — call this out even if the eventual answer is "no, it's already as narrow as the use case
allows."
- Which index the fix targets and why (step 7): the
explain-confirmed candidate list with costs, or the
single statically-matched candidate, or — if multiple indexes contend and no live instance was
available to break the tie — the side-by-side contender comparison and a question to the user asking
which one to change, with no corrected definition given for any of them until they answer.
- The corrected index definition (full JSON or content diff) for the index identified in item 4, with a
one-line justification per change, and the storage/size tradeoff called out explicitly for any
change that triggers one of the pitfalls above (don't let a size cost pass silently just because it
fixes the query).
- The
explain command to run to verify, and what a passing result looks like — but if no live Oak
instance is available, say so plainly and present the static analysis as reasoned-but-unproven rather
than skipping verification silently.
Section B — dynamic fields (ask, don't assess)
For every Section B query, list the construction site and the exact missing input (named OSGi property,
request parameter, config key, or "arbitrary content-authored query — no fixed field exists"), then ask the
user for it. Do not include a coverage table, root cause, or corrected index definition for a Section B
query until the user supplies the concrete field name(s) — at that point re-run steps 4-9 on it and move it
into Section A's report format for that query.
1---2name: tuning-oak-query-indexes3description: 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.4license: Apache-2.05compatibility: Any Jackrabbit Oak-backed repository on AEM 6.5 LTS/AMS using property or Lucene query indexes.6metadata:7 source: https://skillmd.com/skills/adobe/tuning-oak-query-indexes8---910# Tuning Oak Query Indexes1112## Overview1314An Oak query only uses an index for the parts the index actually covers. Any `WHERE`/`ORDER BY`/fulltext15field without a matching, correctly-flagged property definition gets evaluated node-by-node in memory —16same for a wrong nodetype or path scope, **even if every field is indexed**. This skill is the procedure17for finding exactly which fields/conditions aren't covered and what index-definition change fixes each18one.1920**Ground truth for every claim below**: [the AI agent indexing guide](references/ai-agent-indexing-guide.md),21bundled alongside this skill so it works the same way regardless of which codebase it's copied into. Read22it on demand for exact property tables, defaults, cost-model details, and edge cases — this skill is the23checklist, not the reference.2425**This skill assumes nothing about the codebase it's used in beyond "you have a query and an index26definition."** It does not require an AEM instance, a running Oak repository, or a checkout of Oak's own27Java source — those only *strengthen* confidence where noted (step 6 and the "explain can't reach this"28fallback below), never a requirement; the core procedure — read the query, read the index definition, find29the gap, propose the fix — works from the two artifacts alone. Index definitions in a30real project are often FileVault XML (`.content.xml` under an `/apps/.../install` content package,31`jcr:primaryType="oak:QueryIndexDefinition"` and friends), not raw JSON — read them the same way; the32property names and semantics are identical, only the serialization differs.3334## Procedure3536**Required input — the index definition(s). This skill does not run without them.** The whole37assessment is a comparison of a query against the index definition(s) it could use, so the index38definition is mandatory input, not an optional aid. If it was not provided, **ask for it** — accept any39of: a FileVault `.content.xml` under an `/apps/.../install` package, a JSON export, or the live dump at40`GET /system/console/status-oak-index-defn.json` (admin auth). **If none can be obtained, skip this skill41and say so explicitly** — do not emit a coverage assessment from the query alone. A guess about what is42indexed reads as a finding and gets acted on as one; no answer is better than a wrong one. (The query is43the other required input — provide it directly, or build the inventory with the bundled44[extracting JCR queries guide](references/extracting-jcr-queries.md).)4546**Preconditions**: steps 1 and 6 below both mention live-instance tools (`explain`, the Felix47`InventoryPrinter`, Oak's trunk test suite) — neither is required. Given the query text and the48index definition(s), skip those tools' sub-bullets, do steps 2-5 and 7 as pure static analysis, and report49per the final section with verification stated as reasoned-but-unproven.50511. **Get the query and every index definition it could plausibly use** — not just the one you assume52 applies. **If you don't already have the query in hand** — e.g. asked to audit a whole app/bundle rather53 than tune one query someone handed you — first follow the bundled54 [extracting JCR queries guide](references/extracting-jcr-queries.md) to find every55 JCR/QueryBuilder query the codebase actually issues (including ones assembled programmatically through56 wrapper/helper/DAO code, which a text grep alone misses); its output (construction site + every57 trigger/caller path) is the input to this skill's assessment, one query at a time. **If no live instance58 is reachable, work from every index definition you were actually given**59 and say so in the report (§ Report shape) — candidate selection is then reasoned, not confirmed. If a60 live instance *is* reachable, `explain` tells you which index actually gets picked, which is stronger61 than reasoning from the definitions alone; to enumerate every index definition in the repository62 (including non-root ones a per-path lookup would miss), use the Felix `InventoryPrinter` status page63 `oak-core` itself registers — it works on any Sling/Felix-based Oak deployment, not just AEM:64 `GET /system/console/status-oak-index-defn.json` (admin auth). It dumps every index definition (config65 only, no hidden `:data`/`:index` storage nodes) in the same `nam:`/`str:`/`dat:`/... type-prefixed JSON66 encoding oak-run's `--index-definitions-file` uses — one request beats guessing candidate index names67 one at a time.682. **Segregate every query into two sections before analyzing anything** — Section A (static fields) and69 Section B (dynamic fields):70 - **Section A — static fields**: the property *path/name* the query filters, orders, or searches on is71 fixed in source — a literal string or a named constant — even if the *value* compared against it72 varies at runtime (a caller-supplied ID, a loop variable, a request parameter used only as a value).73 A query stays in Section A as long as you can point at the exact `WHERE`/`ORDER BY` field name without74 guessing.75 - **Section B — dynamic fields**: the property *path/name itself* is not fixed in source — it comes from76 OSGi config (`detectMetadataField`-style properties), a request/predicate-map key, a content-authored77 query (`dam:query` nodes, Smart Collections), or a script/native query string. You cannot state which78 property the index needs to cover without external input.79 **Assess Section A fully — that's the primary deliverable.** For Section B, do **not** guess the likely80 or default field name and assess that guess as if it were the query's real target. Instead, list each81 Section B query with the specific missing input (e.g. "OSGi config value of `detectMetadataField`",82 "the `type` request parameter passed to this servlet") and ask the user for it. Only fold a Section B83 query into a real assessment once the user supplies the concrete field name(s) it actually uses in their84 deployment — treat that answer the same as a Section A fact from that point on.8586 | Excuse to assess anyway | Reality |87 |---|---|88 | "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. |89 | "Partial assessment is still useful" | Partial-but-wrong is worse than no assessment — it's indistinguishable from a verified finding in the report. Segregate it into Section B and ask. |90 | "I already covered the default case in Technique 1" | Knowing the *default* is not the same as knowing the *configured* value. Confirm before assessing, don't infer. |91 | "Asking breaks the flow of the report" | The report is still complete — Section A stands on its own. Section B becomes assessable the moment the user answers. |923. **Extract every field/construct the query touches**: `WHERE` equality/range/`IN`/`IS NULL`/93 `IS NOT NULL`, `ORDER BY` columns, `contains(...)`/fulltext, `rep:facet`/`rep:similar`/`rep:suggest`/94 `rep:spellcheck`, path restriction (`ISDESCENDANTNODE`/`//`), and the query's own nodetype restriction95 (`FROM [type]` / XPath `element(*, type)`).96 - **AEM QueryBuilder predicate maps — the field name is usually a *value*, not a key.** For the97 `property`, `relativedaterange`, `daterange`, `boolproperty`, and `similar` predicate types, the map98 key (`1_property`, `5_relativedaterange.property`, ...) is just the predicate-type token plus its99 group index — it names *which predicate*, not *which JCR property*. The actual field the index needs100 to cover is the **string value** assigned to that key, e.g. `map.put("1_property",101 "jcr:content/hasValidMetadata")` → the field to check index coverage for is102 `jcr:content/hasValidMetadata`, read from the *value*, not from the literal text `1_property`. Same103 pattern for `map.put("1_relativedaterange.property", "jcr:created")` → field is `jcr:created`. Don't104 let this trip up the Section A/B call in step 2: the field name here is still a hardcoded string105 literal in source (Section A), even though it doesn't look like a typical `WHERE`-clause field the way106 a SQL2/XPath condition does — it only becomes Section B if *that value itself* is a variable/request107 parameter rather than a literal.1084. **For each field, look up the required flag** (table below) and check the index has a property109 definition with a matching `name` carrying it.1105. **Check the two scoping gotchas before anything else** — these skip the index entirely regardless of111 how well the fields are indexed, and are the most common reason an apparently-correct index isn't112 picked:113 - **Nodetype scoping**: does the query's own `FROM`/`element()` restriction name the index's declared114 type (or a subtype)? If the query is unrestricted (bare `*` / implicit `nt:base`) but the index only115 declares rules for one type (`declaringNodeTypes` on a property index, or `indexRules` not including116 `nt:base` on Lucene), the index is invisible to the planner — cost `Infinity`, or absent from117 the candidate list entirely. Fix by adding the nodetype restriction to the query, not by changing the118 index.119 - **Path scoping** (Lucene only): do `includedPaths` and `queryPaths` match each other, and120 does the query's actual path restriction fall inside them? Leaving `queryPaths` at its default (`/`)121 while narrowing only `includedPaths` is worse than not being selected — the index gets picked outside122 its real coverage and silently returns fewer results than actually exist.1236. **Verify empirically, don't just reason from the spec.** Run `explain <query>` (or the AEM Query124 Performance tool). Confirm: (a) the expected index appears in the candidate/cost log at all, (b) its125 cost beats every alternative including traversal, (c) the plan shows no `traverse` and no unindexed126 residual condition for a field you believe is covered. **A finite cost does not by itself prove a given127 condition is served natively** — live-confirmed: an index selected at a normal-looking finite cost128 (beating traversal, due to nodetype/path scoping alone) had a plan whose actual `luceneQuery` was a bare129 `*:*`, meaning an equality condition on an unindexed property was still being evaluated as a residual130 in-memory filter, not pushed into the index at all. Always read the logged `luceneQuery`/plan text131 itself for the specific field you care about, not just whether *some* index got a finite cost.132 - **`explain` only tells you which index and cost/plan get picked — it says nothing about whether a133 feature that doesn't work through the cost-plan mechanism actually behaves correctly**: suggestions134 (`rep:suggest`), spellcheck (`rep:spellcheck`), facet *counts*, similarity ranking (`rep:similar`),135 and `rep:native` all fall in this category and need their own verification, not an `explain` check.136 See the [indexing guide](references/ai-agent-indexing-guide.md)'s "Verifying features `explain` can't reach" (Appendix B) for the two137 ways to get real signal (a live-instance config/MBean cross-check, or the exact Oak trunk unit tests to138 run) — and if neither is available, say so plainly in the report rather than guessing.1397. **Identify the single existing index the fix belongs on before writing any change.** Never invent a140 brand-new index or silently pick one of several plausible candidates — the target index changes the141 whole recommendation (which properties it already has, its current tags/weight/reindex history), so142 getting it wrong makes step 8's "corrected index definition" wrong too.143 - **Live instance reachable**: run `explain` on the query as-is (even though you expect a gap — the144 candidate list and per-index cost the response returns is exactly what's needed here, not just a145 pass/fail). `explain`'s response enumerates every candidate index it considered with its cost, not146 just the winner — read that list, not just the top line. The lowest-cost index that also matches the147 nodetype/path scoping from step 5 is the target. State the cost numbers you read, not just the index148 name, so the choice is checkable.149 - **No live instance**: enumerate every index definition you were given whose nodetype+path scoping150 (step 5) matches the query — these are the only real candidates regardless of how many index files151 exist in the repo. Then:152 - **Exactly one candidate matches** → that's the target, proceed.153 - **Zero candidates match** → there is no existing index to extend; say so explicitly and propose a154 new index only as a last resort (this is the one case where inventing an index is correct, and it155 still needs to be called out as such, not presented as "the fix" silently).156 - **More than one candidate matches (contending indexes)** → do **not** silently pick one — this is157 exactly the "a property IS indexed, but a *different*, cheaper index still wins" mistake from the158 list above, except now you're the one choosing without the cost data `explain` would have given you.159 Present the contenders side by side — for each: which of the query's fields it already covers,160 its `tags`, `weight` (if set), `includedPaths`/`queryPaths`, and how narrowly it's already scoped —161 and ask the user which index they want the change applied to. Do not propose a corrected definition162 for any of them until the user picks one.1638. **Propose the smallest fix** that gives every field its required flag. Prefer one cohesive index per164 nodetype over several overlapping single-purpose ones. Set `reindex: true` for the change, **not**165 `refresh: true`, unless you can positively confirm **no existing content already carries the166 new/changed property's value** — `refresh: true` only makes a new property definition *plannable*167 (explain will show it as a candidate); it does **not** backfill pre-existing documents that already had168 the value before the definition existed. Live-confirmed: a property added with `refresh: true` alone169 showed up in `explain` immediately, but a real query for a pre-existing document's value returned zero170 results until a genuine `reindex: true` ran. If you can't be certain the property is genuinely new to171 every document, reindex. **Before changing the index, check whether tightening the *query*172 gets you further for less cost** — a more constrained query is always cheaper and safer than a broader173 index, in this order of preference:174 1. Add/narrow a nodetype restriction (`element(*, T)` / `FROM [T]`) — the single highest-leverage change;175 see the nodetype-scoping gotcha above. Never use a bare `*`/`nt:base` restriction if the real target176 type is known.177 2. Add/narrow a path restriction (`ISDESCENDANTNODE`/`//` scoped as tightly as the use case allows) —178 shrinks both the traversal fallback cost and, if the index is path-scoped, keeps it eligible.179 3. Add/tighten property conditions (more selective equality/range conditions push more work into the180 index and reduce what's read out of it).181 Only after the query is as constrained as the use case genuinely allows should the index definition182 itself be widened — widening the index is the more expensive, more permanent change (more storage, more183 indexing time, affects every other query that hits it), while tightening the query text is free and184 query-request-local. **Producing the corrected index definition (the JSON/content diff) is not the same185 as applying it** — writing it out for review is fine to do unprompted, but actually pushing it to a live186 instance (a Sling POST/`curl -F`, a `.content.xml` commit, or any other write) is a repository-mutating187 change like any other and needs the user's explicit go-ahead first, same as the188 [indexing guide](references/ai-agent-indexing-guide.md)'s Appendix B requires for its create/modify/delete commands.1899. **Re-verify with `explain`** after the change has been applied (and after the async cycle, if applicable) —190 confirm no residual/in-memory filtering remains, and any `ORDER BY` is served by the index, not a full191 in-memory sort.192193## Field construct → required index flag194195| Query construct | Property index (`type=property`) | Lucene property definition |196|---|---|---|197| Equality / `IN` | in `propertyNames` | `propertyIndex: true` |198| `IS NOT NULL` | in `propertyNames` (property index has no dedicated existence-only flag) | `propertyIndex: true`, **or** the cheaper `notNullCheckEnabled: true` alone if you never need equality/range/ordering on the value — see the cost note below |199| `IS NULL` | n/a — no mechanism exists for `type=property` | `nullCheckEnabled: true` (standalone, `propertyIndex` not required) |200| `ORDER BY` | not supported | `ordered: true` + explicit `type` |201| `contains(prop, ...)` | not supported | `analyzed: true` |202| `contains(., ...)` (node-scope) | not supported | `analyzed: true` + `nodeScopeIndex: true` on every contributing property |203| `rep:facet` | not supported | `facets: true` |204| Range (`<`,`>`,`<=`,`>=`) | works if in `propertyNames` | `propertyIndex: true` + explicit `type` |205| Relative property (`a/b/@c`) | not supported | `name: "a/b/c"` |206| Function (`lower(...)`, `name()`, `path()`) | not supported | `function: "..."` + `propertyIndex`/`ordered` as needed |207208**Always set an explicit `type`** (`Date`/`Long`/`Double`) on any property used in `ORDER BY` or a range209condition — without it, values can be compared lexicographically (numeric-looking strings sort210`"1","10","2"`; unparsed dates sort *before* all valid ones instead of erroring or being excluded).211212## Index size / storage cost pitfalls213214Every fix in this skill makes a query faster by making the index cover more. That's not free — always215surface the tradeoff when a proposed change triggers one of these, don't just add the flag silently:216217| Enabling this... | ...costs this |218|---|---|219| `nodeScopeIndex` on **any** property in a rule | Indexes the **node name** for every node matching that rule, not just the boosted property — large for broad rules like `nt:base`. Scope the rule to the narrowest real nodetype before turning this on. |220| `evaluatePathRestrictions` | Slight but real per-document storage increase (stores ancestor-path terms). Only enable on indexes that actually need native path-restriction evaluation (§4.2's `queryPaths` correctness case), not by default. |221| `ordered` on a property | Increases index size (doc-values field per ordered property). Intended for single-valued properties only — but enabling it on a multi-valued one does **not** hard-fail: it just logs a `WARN` and skips the doc-values field for that document, so `ORDER BY` silently degrades to an unpredictable position for multi-valued documents instead of erroring. Only mark properties `ordered` that a real `ORDER BY` actually uses, and don't rely on a hard failure to catch a multi-valued mistake — check your data. |222| Many properties in one broad index "just in case" | Bigger index, slower indexing, and — per the nodetype/path scoping gotchas above — no selection benefit if the query doesn't ask for them. Prefer narrow, cohesive indexes over one large catch-all. |223| A single Lucene index approaching **~2^31 documents** | Hard ceiling in the Lucene version Oak ships — the index can stop being openable past this. Split into multiple narrower (nodetype- or path-scoped) indexes (Solr used to be an escape hatch for this; it was removed in Oak 1.82). |224| Long text values with no `maxFieldLength` override | Only the first ~10000 terms per field are indexed by default — a large document field is silently truncated, not indexed in full, which can produce fulltext misses on content past that boundary. |225| `propertyIndex: true` used purely for `IS NOT NULL`/`IS NULL`, with no equality/range/ordering ever needed | Wastes storage — `propertyIndex` indexes every distinct *value* as its own term in a dedicated field. `notNullCheckEnabled`/`nullCheckEnabled` alone (no `propertyIndex` needed) do the same existence check via one lightweight, unstored term in a *shared* field across the whole index (`:notNullProps`/`:nullProps`) — genuinely cheaper, live-confirmed to work standalone. The flip side: `nullCheckEnabled` on a broad/generic nodetype where the property is rarely set can itself create an entry for nearly every document (an entry per node *without* the value) — scope it to a narrow nodetype. |226227**The general principle**: the more constrained a query is, the cheaper and safer it is to serve — a228query naming a specific, narrow nodetype and path is always preferable to a broad `nt:base`/unscoped one,229independent of whatever the index does. Recommend query tightening first (step 8) before recommending an230index that has to compensate for an unnecessarily broad query.231232## Common mistakes (each one live-verified during this skill's development)233234Beyond the two scoping gotchas in step 5 (a nodetype-scoped index skipped entirely when the query has no235matching restriction; `includedPaths`/`queryPaths` mismatches silently returning fewer results):236237- `sync`/`unique` on a Lucene property definition, but the index's own `async` is still a single string238 (not `["async","sync"]`/`["async","nrt"]`) → silently never synchronous, never enforced.239- A property IS indexed, but a *different*, cheaper index still wins → check `weight` (lower = cheaper,240 default `5`), and re-check the scoping gotchas above on the index you expected to win.241- Indexing many unrelated properties into one index "just in case" → prefer one cohesive index per242 nodetype; overlapping indexes with different `includedPaths`/`excludedPaths` for the same nodetype cause243 ambiguous selection.244- `rep:facet`/`rep:similar` hit the nodetype-scoping gotcha too — `rep:facet` fails with a hard exception245 (`"... can't be evaluated by traversal"`) instead of silently falling back like plain fulltext. **This246 only happens when the query actually runs** — `explain` on the same query does not throw; it returns a247 normal success with a `traverse`/`estimatedEntries: Infinity` plan, easy to misread as "fine, just slow."248 Don't trust `explain` succeeding as proof a `rep:facet` query works — run it for real, or treat an249 `Infinity`-cost traverse plan on a facet query as an early-warning sign it will fail at execution.250- A raw `rep:native('idx', 'field:value')` query string silently matches nothing if `field` is an251 `analyzed` property — its real Lucene field name is `full:field` (escape the colon:252 `full\:field:value`), not the bare JCR property name. Only non-analyzed (`propertyIndex`-only) fields253 use the bare name. Root-caused via `FieldNames.ANALYZED_FIELD_PREFIX` and live-confirmed side by side254 (unescaped form: 0 results; `full\:`-escaped form: correct match, same index/content) — this is the255 single most common reason a native query "doesn't work" despite a correct-looking `explain` plan.256257## Report shape258259Structure the report in the two sections from step 2 — never merge them:260261### Section A — static fields (assess fully)262263For every Section A query, produce:2642651. A table: field/construct → covered? → what's missing.2662. Root cause per uncovered field (which failure mode from the table/mistakes above — including the two267 scoping checks, even when they pass).2683. Whether the query itself can be more constrained first (nodetype, then path, then property conditions —269 see step 8) — call this out even if the eventual answer is "no, it's already as narrow as the use case270 allows."2714. Which index the fix targets and why (step 7): the `explain`-confirmed candidate list with costs, or the272 single statically-matched candidate, or — if multiple indexes contend and no live instance was273 available to break the tie — the side-by-side contender comparison and a question to the user asking274 which one to change, with no corrected definition given for any of them until they answer.2755. The corrected index definition (full JSON or content diff) for the index identified in item 4, with a276 one-line justification per change, **and the storage/size tradeoff called out explicitly** for any277 change that triggers one of the pitfalls above (don't let a size cost pass silently just because it278 fixes the query).2796. The `explain` command to run to verify, and what a passing result looks like — but if no live Oak280 instance is available, say so plainly and present the static analysis as reasoned-but-unproven rather281 than skipping verification silently.282283### Section B — dynamic fields (ask, don't assess)284285For every Section B query, list the construction site and the exact missing input (named OSGi property,286request parameter, config key, or "arbitrary content-authored query — no fixed field exists"), then ask the287user for it. Do not include a coverage table, root cause, or corrected index definition for a Section B288query until the user supplies the concrete field name(s) — at that point re-run steps 4-9 on it and move it289into Section A's report format for that query.