AEM search - say goodbye to slow queries

Slow queries are a common problem faced by many AEM developers. There can be various reasons for your AEM queries not being lightning fast, ranging from inefficient queries to sub optimal index definitions.

In this talk, you will learn about:

The talk will focus on specific queries to show examples of various problems and steps to identify and rectify the same.

So are you ready to make your queries lightning fast?

Robert Munteanu

Is it possible to return query results directly from the indexes, without touching other parts of the repository? (sometimes called covering indexes)

royteeuwen

I also tried achieving this, but it currently doesn't work because ACL rules aren't included in the indexes :/, so you could be getting results that you aren't allowed to read

Dennis Fisch

AEMaaCS uses MongoDB as persistence backend. Locally and with RDEs or 6.5 (AMS, on-prem) this is different. Aren't recommendations and index-tuning specific to the persistence backend?

(see answer in talk video)

Nilesh Mali

Any best solution for the most common issue, "The query read or traversed more than 20,000 nodes" ?

puradawid

Plus, why LIMIT 1000 does not help? :-(

Jurgen Brouwer - AmeXio

@puradawid because a limit is often applied after fetching all the results? Edit: let's say you limit a query descending on dates. How would it know which 1000 records to return before getting all the sorted results first?

puradawid

Yeah, for these cases it may not work, but AFAIK it won't work for even simple unordered queries

Jurgen Brouwer - AmeXio

@puradawid as far as I know a query is always sorted somehow, even if it's not visible (or obvious) to you

Dennis Fisch

For AEMaaCS, the first stage any of this can be tested on is a real dev stage. Any idea of when RDEs will use MongoDB in order to be more in-line with actual AEMaaCS stages?

royteeuwen

In the slow queries it tells you about the read optimization percentage. What is this and how can you make this better?

Nitin Gupta

The read optimization is the ratio of number of nodes read vs the number of nodes returned from the index. For instance, if for a query, index returns 100 nodes, but then query engine further filters out 50 nodes (because some constraints were not handled by index) - the read optimization here would only be 50 %. To see how to fix this, the index traversal section of the presentation along with the flow chart can be referred. We would need to make sure that the index actually starts serving all the constraints of the query.

Nilesh Mali

Query debugger Xpath vs SQL2, which one is better to use?

Jelle Bouwmans - AmeXio

Performance will be approximately equivalent as XPath queries are translated into JCR-SQL2.

Jurgen Brouwer - AmeXio

https://experienceleague.adobe.com/en/docs/experience-manager-65/content/implementing/deploying/practices/best-practices-for-queries-and-indexing

Nitin Gupta

Either of the query language is fine, most important factor while trying to optimise queries is to ensure that the index being used serves all the constraints of the query.

Fabian

Is there a way to split indices for nodes without special primary type? I.e. we couldn't achieve a separate index for collections, as they don't have a specific primary type like assets do (dam:Asset). Which meant we had to merge it with the nt:base index which can cause a major impact on performance for large node sets

Konrad Windszus

Why does /oak:index/cqPageLucene in AEMaaCS only consider properties up to level 4 (aggregation). That index is even used for incoming references rail in AEM 6.5...

(see answer in talk video)

Nicolas

If using pagination, how can you get the total number of pages without exceeding the limit?

Diego

Devide the value SearchResults.getTotalMatches() with the value you pass to the p.limit parameter

Konrad Windszus

Where is some documentation about properties which may contain references which are indexed with /oak:index/pathReference/indexRules/nt:base/properties? The names look rather arbitrary (prop_51)

Nitin Gupta

I am not sure if there is a documentation around the various index definitions in AEM - but for the specific query here , the prop_51 in the pathReference has a regex set to ^[^\/]*$, which means it indexes all the properties. However the index also has a valueRegex set at index level, which restricts the type of values from all the properties to be indexes to just "paths". So in effect, prop_51 actually indexes all the properties which have paths (/a/b/c) as values.