The first step in query troubleshooting is often to detect a query is slow, or traverses many nodes. Queries that traverse many nodes are logged as follows:
*WARN* org.apache.jackrabbit.oak.plugins.index.Cursors$TraversingCursor Traversed 22000 nodes with filter Filter(query= select * from [nt:base] where isdescendantnode('/etc') and lower([jcr:title]) like '%coat%'); consider creating an index or changing the query
To get good performance, queries should not traverse more than about 1000 nodes (specially for queries that are run often).
In addition to avoiding queries that traverse many nodes, it makes sense to avoid queries that don’t use an index. Such queries might be fast (and only traverse few nodes) with a small repository, but with a large repository they are typically slow as well. Therefore, it makes sense to detect such queries as soon as possible (in a developer environment), even before the code that runs those queries is tested with a larger repository. Oak will detect such queries and log them as follows (with log level INFO for Oak 1.6.x, and WARN for Oak 1.8.x):
*INFO* org.apache.jackrabbit.oak.query.QueryImpl Traversal query (query without index): select * from [nt:base] where isdescendantnode('/etc') and lower([jcr:title]) like '%coat%'; consider creating an index
To understand why the query is slow, the first step is commonly to get the query execution plan. To do this, the query can be executed using explain select .... For the above case, the plan is:
[nt:base] as [nt:base] /* traverse "/etc//*" where (isdescendantnode([nt:base], [/etc])) and (lower([nt:base].[jcr:title]) like '%coat%') */
That means, all nodes below /etc are traversed.
In order to make the query faster, try to add more constraints, or make constraints tighter. This will usually require some knowledge about the expected results. For example, if the path restriction is more specific, then less nodes need to be read. This is also true if an index is used. Also, if possible use a more specific node type. To understand if a nodetype or mixin is indexed, consult the nodetype index at /oak:index/nodetype, property declaringNodeTypes. But even if this is not the case, the nodetype should be as specific as possible. Assuming the query is changed to this:
select * from [acme:Product] where isdescendantnode('/etc/commerce') and lower([jcr:title]) like '%coat%') and [commerceType] = 'product'
The only relevant change was to improve the path restriction. But in this case, it already was enough to make the traversal warning go away.
After changing the query, there is still a message in the log file that complains the query doesn’t use an index, as described above:
*INFO* org.apache.jackrabbit.oak.query.QueryImpl Traversal query (query without index): select * from [acme:Product] where isdescendantnode('/etc/commerce') and lower([jcr:title]) like '%coat%' and [commerceType] = 'product'; consider creating an index
The query plan of the index didn’t change, so still nodes are traversed. In this case, there are relatively few nodes because it’s an almost empty development repository, so no traversal warning is logged. But for production, there might be a lot more nodes under /etc/commerce, so it makes sense to continue optimization.
If it is known from the data model that a query will never traverse many nodes, then no index is needed. This is a corner case, and only applies to queries that traverse a fixed number of (for example) configuration nodes, or if the number of descendant nodes is guaranteed to be very low by using a certain nodetype that only allows for a fixed number of child nodes. If this is the case, then the query can be changed to say traversal is fine. To mark such queries, append option(traversal ok) to the query. This feature should only be used for those rare corner cases.
select * from [nt:base] where isdescendantnode('/etc/commerce') and lower([jcr:title]) like '%coat%' and [commerceType] = 'product' option(traversal ok)
To find out how many nodes are in a certain path, you can use the JMX bean NodeCounter, which can estimate the node count. Example: run getEstimatedChildNodeCounts with p1=/ and p2=2 might give you:
/: 2522208, ... /etc: 1521504, /etc/commerce: 29216, /etc/images: 1231232, ...
So in this case, there are still many nodes below /etc/commerce in the production repository. Also note that the number of nodes can grow over time.
To avoid running queries that don’t use an index altogether, you can change the configuration in the JMX bean QueryEngineSettings: if you set FailTraversal to true, then queries without index will throw an exception when trying to execute them, no matter how many nodes are in the repository. This doesn’t mean queries will never traverse over nodes, it just means that queries that must traverse over nodes will fail.
There are multiple options:
The last plan is possibly the best solution for this case.
In case you need to modify or create a Lucene property index, you can use the Oak Index Definition Generator tool.
As the tool doesn’t know your index configuration, it will always suggest to create a new index; it might be better to extend an existing index. However, note that:
After changing the query, and possibly the index, run the explain select again, and verify the right plan is used, in this case that might be, for the query:
select * from [acme:Product] where isdescendantnode('/etc/commerce') and contains([jcr:title], 'Coat') and [commerceType] = 'product' [nt:unstructured] as [acme:Product] /* lucene:lucene(/oak:index/lucene) full:jcr:title:coat ft:(jcr:title:"Coat")
So in this case, only the fulltext restriction of the query was used by the index, but this might already be sufficient. If it is not, then the fulltext index might be changed to also index commerceType, or possibly to use evaluatePathRestrictions.