Indexes going in Traversal

[#1]

Hi ,

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.

OOTB ntBaseLucene

Numdocs count - 5725504

/oak:index/ntBaseLucene

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 ?

[#2]

I have got similar issue. 

I have reindexed all OOTB indexes and setting reindex flag = true , then this issue got resolved.

[#3]

Hi @Kundan_Ray1,

Oak chooses the indexer with the lowest estimated cost. (https://docs.adobe.com/content/help/en/experience-manager-65/deploying/deploying/queries-and-indexin...).
You can go to Tools > Operations > Dashboard > Diagnosis > Query Performance > Explain Query (/libs/granite/operations/content/diagnosistools/queryPerformance.html), execute you query and check costs for ntBaseLucene vs traversal.

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.)

Regards

[Kundan_Ray1]

It's not clear to me if this is your query or its an OOTB query ? It's searching "nt:base". The Oak index definition suggest such query to have an index with the following properties:

- compatVersion = 2
- async = "async"
- jcr:primaryType = oak:QueryIndexDefinition
- evaluatePathRestrictions = true
- type = "lucene"
+ indexRules
+ nt:base
+ properties
+ master
- name = "cq:master"
- propertyIndex = true
- notNullCheckEnabled = true

If the existing index does not have the suggested properties you may add it and rebuild the index to verify if it makes any improvements.

[Kundan_Ray1]

This is 6.5.5 OOTB Query using OOTB index /oak:index/ntBaseLucene/indexRules/nt:base/properties/master going in index-traversal

[#6]

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.