Using AEM's Query Debugger to Optimise Slow Searches
AEM repositories have an inconvenient habit of doubling in size every couple of years without anyone noticing, until the day a familiar search takes a slow breath and never fully recovers. Australian teams running newsroom sites out of Sydney or retail catalogues out of Melbourne tend to hit the same wall: asset pickers and content searches that should feel snappy start hesitating. The Query Debugger built into AEM offers a fairly direct path to diagnosis without diving into log scrapers.
The endpoint to bookmark is /libs/cq/search/content/querydebug.html. Set it to SQL2 for most tasks, and the screen returns both the explain plan and an index coverage verdict. Oak either walks the JCR tree or hits a Lucene index. Walking is slow and scales linearly with repository size; indexed lookups return in milliseconds. Reading which one Oak chose is the first diagnostic win.
What follows is a walk-through of how to read that output, where index gaps hide, and a handful of recommendations for a quick performance audit. Practitioners who follow the CIRCUIT site often share tactics for proactive index hygiene that echo what this tool surfaces every day.
Why Search Latency Hurts AEM-Driven Sites
Every JCR query resolves through Oak, which answers searches either by reading a Lucene index or by walking the content tree from a starting path. Indexed lookups return in milliseconds; tree walks can stretch into seconds. When the Query Debugger flags a missing index, every author action that uses the query pays that price, and the cost grows with the workspace beneath the searched path.
There is a quieter second-order impact. A long traversal holds thread-pool slots and slows unrelated Oak operations on the same instance. Smaller Australian production clusters, often sized for cost rather than headroom, feel this earlier than their North American counterparts. Treating query performance as a one-off cleanup misses the bigger pattern: new templates add new property shapes, marketing campaigns bring entire subtrees, and integrations push node types nobody remembered to index. The Query Debugger earns its place when teams revisit it on each release, not only when authors complain.
Accessing and Configuring the Query Debugger
Administrators usually scope access to a small developer group rather than opening it broadly, since the screen includes raw SQL2 and access patterns that compliance teams often want logged. On Australian federal installations the access trail tends to be reviewed quarterly, which is a sensible cadence for diagnostic tooling.
Set the language to SQL2 for most work, since it maps closely to the queries that Sling Models, Sightly, and Java services emit at runtime. Pick a starting path that mirrors production, typically /content for page searches or /content/dam for asset lookups. Keep the result limit modest during exploration; twenty rows usually confirm whether an index is firing.
Engineers who care about reliability elsewhere in their stack tend to apply the same verification mindset here as they would for the outbox pattern: confirm the path matches the expectation rather than trusting the framework silently. The explain plan and the index coverage panel are the two outputs worth recording on every audit run.
Reading the Explain Plan and Index Coverage Output
Once a query runs, the explain plan draws a tree of decisions: which index Oak consulted, which filter clauses were pushed down to Lucene, and which clauses had to be applied in memory. Indexed BooleanQuery nodes collapse into compact shapes; traversal paths sprawl into many child nodes that resemble a verbose switch statement. The shape of the tree alone often points to where time is being spent.
Index coverage is the second signal to watch. When the panel lists a property without an associated index, Oak loads candidate nodes and filters them in memory. Even a modest repository can turn this into a multi-second operation once a campaign drives tens of thousands of matches. Order matters too: a query that filters on indexed properties first and only then applies unindexed constraints can still run quickly, while the opposite order inverts the advantage. Scan the leaves of the explain plan; if the most selective filter sits beneath an unindexed branch, that is the place to start.
Closing Index Gaps with Custom Oak Indexes
When the Query Debugger exposes a coverage gap, the usual fix is a new entry under /oak:index. Lucene full-text indexes handle text predicates efficiently, while property indexes target exact matches on String, Boolean, or Date fields. Picking the right kind starts with the predicate's shape: LIKE '%abc%' usually wants a full-text index, property = 'value' is better served by a property index.
Defining the index is only half the job; verifying it locally is the other half. Engineers often run local AEM instances with embedded databases such as Apache Derby for fast iteration, allowing the same query to run through the Query Debugger against a representative dataset. A green explain plan locally is the strongest early signal that the new index will behave under load.
Pay attention to the reindex flag when shipping changes. Oak rebuilds Lucene indexes asynchronously, and a large reindex can briefly degrade search performance. Schedule reindex windows for low-traffic periods and pair the change with a runbook entry so the next engineer understands why a five-minute delay was expected at 2 AM AEST.
Anti-Patterns That Cripple Query Speed
The most expensive queries tend to share a small set of habits. Wildcard predicates that escape the leading character force a full repository scan because Lucene can no longer anchor its term dictionary. Sorting by an unindexed property is the other classic trap, because Oak has to materialise every result before returning even the top twenty items.
Two more pitfalls round out the list. Restricting the starting path to / instead of a narrower subtree hands Oak more nodes than it can crawl efficiently, and combining LIKE '%term%' with ORDER BY on a different field multiplies the pain. Spotting these shapes in the explain plan usually points to a quick fix: a tighter path, a fuller index, or a reduced result set.
Practical Steps for a Quick Query Audit
Run the Query Debugger against the slowest searches identified by APM traces or by author feedback. Capture the explain plan, the index coverage panel, and the candidate count for each query, then rank the findings by impact. A consistent checklist keeps audits aligned across releases and across teams.
- Start every audit from the Query Debugger and treat its verdict as ground truth.
- Tighten result limits in custom code; pagination is a feature, not a luxury.
- Add Oak indexes for any property that appears in a WHERE clause more than once.
- Prefer property indexes for exact matches and Lucene full-text indexes for predicates containing wildcards.
- Reindex asynchronously outside business hours; never block a release on a synchronous reindex.
- Capture query timings in a small dashboard so the regression story is visible at a glance.
- Document the index contract so each new template inherits a search-friendly property layout.
Time spent in the Query Debugger pays itself back the first time a curious engineer asks why a popular search quietly doubled in latency. The gains compound with every new template, every campaign, and every integration that lands on the repository. Practitioners who want to go deeper into AEM performance patterns can register for the event, where hands-on sessions continue the conversation this article only opens.