Add includedPaths and queryPaths to the oak:QueryIndexDefinition node (e.g. /oak:index/customIndex) on all custom indexes to constrain the index. This will make it so the index only handles queries that have the path included (e.g. If the query starts with path /content/mysite - /jcr:root/content/mysite[@foo=1] then includedPaths and queryPaths (String) properties would both have a value /content/mysite
Known issues about abnormal slowness of custom JCR queries on AEM
This page will contain the known issues causing abnormal query execution time, as well as their resolution.
If you do not find your issue in this list, we'd redirect to the "Troubleshooting Slow Queries" documentation page in order to investigate further.
Issue 1 - Slowness caused by too high index cost
On certain environments a custom JCR or QueryBuilder query that uses a custom lucene property index (oak:QueryIndexDefinition) is slow on some AEM environments where there is a lot of content.
AEM 6.3, AEM 6.4
The cost returned by the index for execution could be so high now (due to content volume on the system) that another index is getting used for the query instead.
It is likely that the wrong index is getting used for the query. The Explain Query Tool (Tools => Operations => Diagnosis => Query Performance => Explain Query) can be used to debug this.