Although we have indexes (property,lucene ) defined for most of the queries, few of the indexes are going in traversal
For Ex :
On Restart of AEM
*WARN* [Apache Sling Repository Startup Thread] org.apache.jackrabbit.oak.plugins.index.search.spi.query.FulltextIndex$FulltextPathCursor Index-Traversed 140000 nodes with filter Filter(query=SELECT * FROM [nt:base] WHERE ISDESCENDANTNODE([/content]) AND [cq:master] IS NOT NULL, path=/content//*, property=[cq:master=[is not null]])
Therefore it's taking a long time for restart.
Numdocs count - 5725504
Property Field Info - /oak:index/ntBaseLucene cq:master 165543
But logs we see it's going for Index-Traversed although we have index defined.
What is the reason for Index Traversal ? This is happening for lower numdocs indexes also. How do we fix this ?
If the cost for ntBaseLucene is higher, it might indicate that indexes are broken/not actual due to various reasons (such as improper instance shutting down during indexing) and thus triggering re-index might be considered for ntBaseLucene (set the reindex property to true within /oak:index/ntBaseLucene). It is best to re-index during quiet periods (for example, not during a large content ingest), and ideally during maintenance windows when AEM's load is known and controlled (https://docs.adobe.com/content/help/en/experience-manager-65/deploying/deploying/troubleshooting-oak...).
However, I guess querying is not only reason for slow instance starting up. While activation the MSM core bundle ("Day Communique 5 WCM Multi Site Management Core") invokes the query you mentioned - "SELECT * FROM [nt:base] WHERE ISDESCENDANTNODE([/content]) AND [cq:master] IS NOT NULL" and checks if found nodes exist (please see the stack trace in the thread com.day.cq.wcm.msm.impl.LiveCopyFinderProvider.buildBloomFilter -https://experienceleaguecommunities.adobe.com/t5/adobe-experience-cloud-ideas/msm-core-fails-to-star...). The query returns live copy configuration nodes (e.g. /content/we-retail/us/en/community/members/jcr:content/cq:LiveSyncConfig) and according to your message, the number of found nodes is large (>Numdocs count - 5725504) so processing them might introduce a delay. I'd also recommend reviewing the content structure to ensure that no unnecessary nodes are present (such as unnecessary pages + their live copies, etc.)
Hi @Andrei_Dantsou , Thanks for your inputs . We have given our inputs to Adobe Product Engineering as we have seen this issue happening only in 6.5 and not in 6.3 , with the same number of livecopy nodes present there. As this is a generic search query triggered during restart where result count is more than 10k, it goes for index-traversal. In future, this may be removed from startup msm core bundle activation to on-demand.