Index Definition for ISDESCENDANTNODE is not working in Query Explain

[aemUser2345]

I have added the index definition in the oak-index folder. I made sure it was indexed correctly. However, when I ran the query explain of the below query, the index was not being used. The index definition is supposed to improve the query below because it is trying to access the descendent nodes inside costco homepage. 

SELECT s FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/costco/homepage/jcr:content]) 

The warning I got from index definition is:

no proper index was found for filter Filter(query=explain SELECT s FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/costco/homepage/jcr:content]) , path=/content/costco/jcr:content//*) Traversal query (query without index): explain SELECT s FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/costco/homepage/jcr:content]) ; consider creating an index

Here's the index definition I used:

 <pageDecendents jcr:primaryType="oak:QueryIndexDefinition"
                 reindex="{Boolean}true"
                 reindexCount="{Long}1"
                 seed="{Long}-4679589122988751568"
                 type="path">
   <includedPaths jcr:primaryType="nt:unstructured">
       <content jcr:primaryType="nt:unstructured" includePatterns="/content/costco/.*"/>
   </includedPaths>
   <indexRules jcr:primaryType="nt:unstructured">
       <nt:base jcr:primaryType="nt:unstructured">
             <properties jcr:primaryType="nt:unstructured">
                  <path jcr:primaryType="nt:unstructured" name="jcr:path" nodeScopeIndex="{Boolean}true" propertyIndex="{Boolean}false" unique="{Boolean}false"/>
             </properties>
      </nt:base>
    </indexRules>
</pageDecendents>

 Am I missing anything? Thanks

[DEBAL_DAS]

Hi @aemUser2345 ,

Could you please help me to understand the points below -

1. How did you generate the index definition? Did you use Oak Index Definition Generator (https://oakutils.appspot.com/generate/index) to generate the index definition here?

If you use Oak Index Definition Generator, then you will be seeing following index definition as shown below -

2. Are you trying to access descendent nodes of any specific node type like cq:PageContent/nt:unstructured with your query? Why are you using nt:base in your query?

[aemUser2345]

For some context, I need to access component nodes in all pages. So that's why there's a regex in the includedPath so it only contains regex of the root folder. I have asked a similar question a few days ago and it worked for the first time in my local: https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/include-parent-path-in-ind...

But I am not sure why it doesn't work anymore. I did set reindex to true but the reindex count has been 1 only.

[DEBAL_DAS]

got it. 

Please create an index definition similar to the one below and give a try -

<jcr:root xmlns:oak="http://jackrabbit.apache.org/oak/ns/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0" xmlns:rep="internal" jcr:mixinTypes="[rep:AccessControllable]" jcr:primaryType="nt:unstructured">
<customIndex jcr:primaryType="oak:QueryIndexDefinition" async="[async]" compatVersion="{Long}2" evaluatePathRestrictions="{Boolean}true" includedPaths="[/content/we-retail]" queryPaths="[/content/we-retail]" reindex="{Boolean}false" reindexCount="{Long}7" seed="{Long}7702165159190351870" type="lucene">
<aggregates jcr:primaryType="nt:unstructured">
<nt:unstructured jcr:primaryType="nt:unstructured">
<include0 jcr:primaryType="nt:unstructured" path="jcr:content/*"/>
</nt:unstructured>
</aggregates>
<indexRules jcr:primaryType="nt:unstructured">
<nt:unstructured jcr:primaryType="nt:unstructured"/>
</indexRules>
</customIndex>
</jcr:root>

I have used following JCR SQL2 query : SELECT * FROM [nt:unstructured] AS s WHERE ISDESCENDANTNODE([/content/we-retail/us/en/experience/jcr:content]) to generate this index definition.

 I think this will help you to fix the indexing issue and share your feedback.

[DEBAL_DAS]

<customIndex jcr:primaryType="oak:QueryIndexDefinition" async="[async]" compatVersion="{Long}2" evaluatePathRestrictions="{Boolean}true" includedPaths="[/content/we-retail]" queryPaths="[/content/we-retail]" reindex="{Boolean}false" reindexCount="{Long}7" seed="{Long}7702165159190351870" type="lucene">
<aggregates jcr:primaryType="nt:unstructured">
<nt:unstructured jcr:primaryType="nt:unstructured">
<include0 jcr:primaryType="nt:unstructured" path="jcr:content/*"/>
</nt:unstructured>
</aggregates>
<indexRules jcr:primaryType="nt:unstructured">
<nt:unstructured jcr:primaryType="nt:unstructured"/>
</indexRules>
</customIndex>

[aemUser2345]

thanks for your help but unfortunately it is still not working It is still showing index without query. Is it working on your end? 

[DEBAL_DAS]

yes, it is working for me. Please do re-index and give a try.

[SureshDhulipudi]

Did you check the reindexing completed successfully after creating index definition?

as you set the <reindex> to true - it should run.

Verify the reindexCount value - it should have increased.

second

can you check with complete path in your query (/content/costco/homepage/jcr:content)

[aemUser2345]

Yes here are what i have tried:

• I did set reindex to true and the value did increase.
• I also did check the complete path and the page exists.
• I have also tried some different pages as well but its not working either.
• I have checked the sling logs to make sure the reindexing has completed.

They have the same warning of "query without indexing"

[kautuk_sahni]

@aemUser2345 Did you find the suggestions from users helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.