Custom Index not getting picked

[Shankar_K]

Hi I am trying to create a custom Lucene index in AEM as Cloud service

Query 
/jcr:root/content/projectname/us//element(*, cq:Page)[(jcr:content/@customprop4 = 'true')] option (index tag customtag)

Index Definition
<hub.name-1-custom-1 jcr:primaryType="oak:QueryIndexDefinition" async="[async]" compatVersion="{Long}2" evaluatePathRestrictions="{Boolean}true" includedPaths="[/content/projectname/us/en]" queryPaths="[/content/projectname/us/en]" reindex="{Boolean}false" reindexCount="{Long}5" seed="{Long}8015621631852823939" tags="[customtag]" type="lucene" >
<indexRules jcr:primaryType="nt:unstructured">
<cq:Page jcr:primaryType="nt:unstructured">
<properties jcr:primaryType="nt:unstructured">
<template jcr:primaryType="nt:unstructured" name="jcr:content/cq:template" propertyIndex="{Boolean}true"/>
<customprop1 jcr:primaryType="nt:unstructured" name="jcr:content/customprop1" ordered="{Boolean}true"/>
<tags jcr:primaryType="nt:unstructured" name="jcr:content/cq:tags" propertyIndex="{Boolean}true"/>
<customprop2 jcr:primaryType="nt:unstructured" name="jcr:content/customprop2" ordered="{Boolean}true"/>
<customprop3 jcr:primaryType="nt:unstructured" name="jcr:content/customprop3" ordered="{Boolean}true"/>
<customprop4 jcr:primaryType="nt:unstructured" name="jcr:content/customprop4" propertyIndex="{Boolean}true"/>
</properties>
</cq:Page>
</indexRules>
</hub.name-1-custom-1>

Im getting No indexes where used 
if i remove the tag from Query cqPageLucene is getting used

How to resolve this ?

[TarunKumar]

Hi @Shankar_K ,

I'd suggest few steps which you can follow:-

1. Test and debug querybuilder queries, you can use the QueryBuilder debugger console at
http://localhost:4502/libs/cq/search/content/querydebug.html

2. Reindex the cqPageLucene Index and check the cost of the query and index used in your query. You can check the cost or query performance in below link at http://localhost:4502/libs/granite/operations/content/diagnosistools/queryPerformance.html .

3. Debug query with your own logger and writer, this may give you some clue

https://experienceleague.adobe.com/en/docs/experience-manager-65/content/implementing/deploying/conf...

-Tarun

[Shankar_K]

Hi Tarun,

         I have followed the steps mentioned i am not getting any errors or warnings for the Query and Index , additionally when i run the SQL 2 query with same the index is getting used but for Xpaths it  not picking the index

[Anil_Chennapragada]

hi @Shankar_K ,

Hope you have figured solution for the above, if not few references below.

1. Below are good references of how you can use respective property index / lucene indexing, test the indexes and deploying it to the higher instances
   1. https://www.aemcq5tutorials.com/tutorials/aem-oak-indexing-comprehensive-guide/
   2. https://medium.com/@shubhanshu.singh/aem-fulltext-lucene-search-implementation-part-1-cd99687ff736
   3. Deploying lucene index - https://experienceleague.adobe.com/en/docs/experience-manager-cloud-service/content/operations/index...

 Thanks,

Anil

[aanchal-sikka]

@Shankar_K 

Can you please check the format of tag in index? It should be 

tags = ["customAdvSearch"]

while the snippet that you shared has tags="[customtag]" 

Sharing a sample from https://experienceleague.adobe.com/en/docs/experience-manager-learn/foundation/development/understan...

• Use tags property to tag the index and while querying specify this tags value to use the index. The general query syntax is <query> option(index tag <tagName>).

/oak:index/wknd.adventures-1-custom-1
    - jcr:primaryType = "oak:QueryIndexDefinition"
    - type = "lucene"
    - compatVersion = 2
    - async = ["async", "nrt"]
    - includedPaths = ["/content/wknd"]
    - queryPaths = ["/content/wknd"]
    - evaluatePathRestrictions = true
    - tags = ["customAdvSearch"]
...

[arunpatidar]

Hi @Shankar_K 

Can you try adding in index property

selectionPolicy = "tag"

https://jackrabbit.apache.org/oak/docs/query/query-engine.html#index-selection-policy

Example

  - compatVersion = 2
  - async = "async"
  - selectionPolicy = "tag"
  - queryPaths = [/content/projectname/us]
  - includedPaths = [/content/projectname/us]
  - jcr:primaryType = oak:QueryIndexDefinition
  - evaluatePathRestrictions = true
  - type = "lucene"
  - tags = [customtag]
  + indexRules 
   + cq:Page 
    + properties 
     + customprop4 
      - name = "jcr:content/customprop4"
      - propertyIndex = true

[Shankar_K]

Hi Everyone the main issue was the index i created the index that was interferring with OOTB cqPageLucene as well as OOTB ntbaseLucene-2 when ,i added the path restrictions intially this was happening then i tried to copy and rename the OOTB page lucene and added my custom properties it worked , im still figuring out this behaviour ,but index is picked up and working as expected , is there any reason for this behavior?

[aanchal-sikka]

@Shankar_K 

• Please do not rename the OOTB index. The system runs various queries and might need the OOTB one,
• When we add path restrictions, we also need to assure that target query should also be done on this path or its sub-tree
  • In the example that you have shared query is on "/jcr:root/content/projectname/us/", but path restriction is on "/jcr:root/content/projectname/us/en". It should be reverse.

Query: When you use Explain query, to see which index is picked up, did it mention that your custom was also evaluated? If yes, does it have a higher cost?

[Shankar_K]

Hi @aanchal-sikka  yes i have checked the custom index being picked for Query and  i can see the queries using OOTB pageLucene working fine and still they are using the OOTB pageLucene , i have made the path   changes you mentioned its working fine, the cost for custom index is also  less compared to other indexes . and if i want to delete the index from cloud environment how can i do that ?

[aanchal-sikka · Accepted solution]

Please refer to https://experienceleague.adobe.com/en/docs/experience-manager-cloud-service/content/operations/index... 

To achieve the effect of removing a custom index or customization, create a new version of the custom or customized index with a definition that effectively simulates the removal of the index.

Once a new version of an index is deployed, the older version of the same index will no longer be used by queries.
The older version will not be immediately deleted from the environment,
but will become eligible for garbage collection by a clean-up mechanism that runs periodically.

Removing a Fully Custom Index

Follow the steps described in Undoing a Change using a dummy index as the new version. A dummy index is never used for queries and does not contain any data, so the effect is the same as if the index did not exist. For this example, you can name it /oak:index/acme.product-custom-3. This name replaces the index /oak:index/acme.product-custom-2. An example of such a dummy index is:

<acme.product-custom-3
        jcr:primaryType="oak:QueryIndexDefinition"
        async="async"
        compatVersion="2"
        includedPaths="/dummy"
        queryPaths="/dummy"
        type="lucene">
        <indexRules jcr:primaryType="nt:unstructured">
            <rep:root jcr:primaryType="nt:unstructured">
                <properties jcr:primaryType="nt:unstructured">
                    <dummy
                        jcr:primaryType="nt:unstructured"
                        name="dummy"
                        propertyIndex="{Boolean}true"/>
                </properties>
            </rep:root>
        </indexRules>
</acme.product-custom-3>

[Shankar_K]

<?xml version="1.0" encoding="UTF-8"?>
<jcr:root
xmlns:sling="http://sling.apache.org/jcr/sling/1.0"
xmlns:jcr="http://www.jcp.org/jcr/1.0"
xmlns:oak="http://jackrabbit.apache.org/oak/ns/1.0"
xmlns:cq="http://www.day.com/jcr/cq/1.0"
xmlns:nt="http://www.jcp.org/jcr/nt/1.0"
jcr:primaryType="oak:QueryIndexDefinition"
async="[async,nrt]"
compatVersion="{Long}2"
evaluatePathRestrictions="{Boolean}true"
includedPaths="[/content/projectname]"
queryPaths="[/content/projectname]"
type="lucene">
<aggregates jcr:primaryType="nt:unstructured">
<cq:Page jcr:primaryType="nt:unstructured">
<include0
jcr:primaryType="nt:unstructured"
path="jcr:content"
relativeNode="{Boolean}true"/>
</cq:Page>
<nt:file
jcr:primaryType="nt:unstructured">
<include0
jcr:primaryType="nt:unstructured"
path="jcr:content"/>
</nt:file>
<cq:PageContent
jcr:primaryType="nt:unstructured">
<include0
jcr:primaryType="nt:unstructured"
path="*"/>
<include1
jcr:primaryType="nt:unstructured"
path="*/*"/>
<include2
jcr:primaryType="nt:unstructured"
path="*/*/*"/>
<include3
jcr:primaryType="nt:unstructured"
path="*/*/*/*"/>
</cq:PageContent>
</aggregates>
<indexRules jcr:primaryType="nt:unstructured">
<cq:Page
jcr:primaryType="nt:unstructured">
<properties
jcr:primaryType="nt:unstructured">
<cqLastModified
jcr:primaryType="nt:unstructured"
name="jcr:content/cq:lastModified"
ordered="{Boolean}true"
propertyIndex="{Boolean}true"
type="Date"/>
<jcrTitle
jcr:primaryType="nt:unstructured"
name="jcr:content/jcr:title"
nodeScopeIndex="{Boolean}true"
propertyIndex="{Boolean}true"
type="String"
useInSpellcheck="{Boolean}true"
useInSuggest="{Boolean}true"/>
<pageTitle
jcr:primaryType="nt:unstructured"
name="jcr:content/pageTitle"
nodeScopeIndex="{Boolean}true"
propertyIndex="{Boolean}true"
type="String"
useInSpellcheck="{Boolean}true"
useInSuggest="{Boolean}true"/>
<navTitle
jcr:primaryType="nt:unstructured"
name="jcr:content/navTitle"
nodeScopeIndex="{Boolean}true"
propertyIndex="{Boolean}true"
type="String"
useInSpellcheck="{Boolean}true"
useInSuggest="{Boolean}true"/>
<nodeName
jcr:primaryType="nt:unstructured"
name=":nodeName"
nodeScopeIndex="{Boolean}true"
propertyIndex="{Boolean}true"
type="String"
useInSpellcheck="{Boolean}true"
useInSuggest="{Boolean}true"/>
<cqLastRolledoutBy
jcr:primaryType="nt:unstructured"
name="jcr:content/cq:lastRolledoutBy"
propertyIndex="{Boolean}true"
type="String"/>
<location
jcr:primaryType="nt:unstructured"
name="jcr:content/location"
notNullCheckEnabled="{Boolean}true"
propertyIndex="{Boolean}true"
type="String"/>
<customprop1
jcr:primaryType="nt:unstructured"
name="jcr:content/customprop1"
ordered="{Boolean}true"/>
<customprop2
jcr:primaryType="nt:unstructured"
name="jcr:content/customprop1"
propertyIndex="{Boolean}true"/>
<customprop3
jcr:primaryType="nt:unstructured"
name="jcr:content/customprop3"
ordered="{Boolean}true"/>
<customprop4
jcr:primaryType="nt:unstructured"
name="jcr:content/customprop5"
ordered="{Boolean}true"/>
<customprop5
jcr:primaryType="nt:unstructured"
name="jcr:content/customprop5"
propertyIndex="{Boolean}true"/>
<customprop6
jcr:primaryType="nt:unstructured"
name="jcr:content/customprop6"
propertyIndex="{Boolean}true"/>
<customprop7
jcr:primaryType="nt:unstructured"
name="jcr:content/customprop7"
propertyIndex="{Boolean}true"/>
</properties>
</cq:Page>
</indexRules>
</jcr:root>

After deploying the index with path restrictions i have an issue SQL2 queries are not giving correct results but if i delete the index its working fine what might be the issue

Its working for /jcr:root/content/contenthub/us/en//element(*, cq:Page)[(jcr:content/@customprop5 = 'true')] working fine

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE('/content/projectName') AND s.[jcr:content/cq:template]= '/conf/projectName/settings/wcm/templates/custom-page' ORDER BY [jcr:content/customprop3] DESC

cost for [/oak:index/custom-index] of type (lucene-property) with plan [lucene:custom-index indexDefinition: /oak:index/custom-index estimatedEntries: 593 luceneQuery: :ancestors:/content/projectName/us/en/page sortOrder: [{ propertyName : jcr:content/customProp3, propertyType : UNDEFINED, order : DESCENDING }] ] is 297.50

I can see the results and count in Queryperformance but the SQL 2 query is not working as expected from java code its returning results randomly but if i delete the index its working fine what might be the issue?

[Shankar_K]

But when i remove the project specfic path restriction and add excludedPaths String[]  /var, /etc/replication, /etc/workflow/instances, /jcr:system this is not breaking but for SQL 2 instead of my index ntBaseLucene is getting used

[Shankar_K]

Hi @arunpatidar when i add path restrictions thats giving improper results when i  use the same path restrictions as cq page lucene its working fine,what might be the issue is my index a valid one?