How to solve query indexing issue?

[nandan123]

I have workflow "torrid-pdp-top-sheet" which does not have any process steps which contains query, but I am getting below error:

04.05.2023 05:54:34.559 *INFO* [JobHandler: /var/workflow/instances/server2/2023-05-03/torrid-pdp-top-sheet_414:/content/dam/torrid/pdp-assets/197/314/19731487] org.apache.jackrabbit.oak.plugins.index.lucene.LucenePropertyIndex Change in index version detected. Query would be performed without offset
04.05.2023 05:55:11.175 *WARN* [JobHandler: /var/workflow/instances/server2/2023-05-03/torrid-pdp-top-sheet_414:/content/dam/torrid/pdp-assets/197/314/19731487] org.apache.jackrabbit.oak.plugins.index.search.spi.query.FulltextIndex$FulltextPathCursor Index-Traversed 10000 nodes with filter Filter(query=select [jcr:path], [jcr:score], * from [cq:Workflow] as a where [status] = 'RUNNING' and isdescendantnode(a, '/') order by [startTime] desc /* xpath: /jcr:root//element(*,cq:Workflow)[@status='RUNNING'] order by @startTime descending */, path=//*, property=[status=[RUNNING]])

Any assistance in solving the issue is much appreciated.

Thanks,

Nandan

[MayurSatav]

hi @nandan123 ,

Could you please check if the index is up-to-date, you can check the status of the index by going to the AEM console and navigating to Tools > Operations > Lucene Indexes. If the index is not up-to-date, try rebuilding it by clicking on the "Rebuild" button in the AEM console. 

[Saravanan_Dharmaraj]

@nandan123 you can check the oak index generator to create simple indexes. But i assume you haven't added this query in the workflow. You can run below query in explain query tool and see the cost. 

https://oakutils.appspot.com/generate/index

Explain query tool

http://localhost:4502/libs/granite/operations/content/diagnosistools/queryPerformance.html 

query:

select [jcr:path], [jcr:score], * from [cq:Workflow] as a where [status] = 'RUNNING' and isdescendantnode(a, '/') order by [startTime] desc

index

{
    "compatVersion": 2,
    "async": "async",
    "jcr:primaryType": "oak:QueryIndexDefinition",
    "evaluatePathRestrictions": true,
    "type": "lucene",
    "indexRules": {
        "jcr:primaryType": "nt:unstructured",
        "cq:Workflow": {
            "jcr:primaryType": "nt:unstructured",
            "properties": {
                "jcr:primaryType": "nt:unstructured",
                "startTime": {
                    "name": "startTime",
                    "ordered": true,
                    "jcr:primaryType": "nt:unstructured"
                },
                "status": {
                    "name": "status",
                    "propertyIndex": true,
                    "jcr:primaryType": "nt:unstructured"
                }
            }
        }
    }
}

https://www.linkedin.com/pulse/aem-struggling-decide-how-add-oak-index-veena-vikraman/ 

[nandan123]

Thank you @Saravanan_Dharmaraj for your response.

I have already defined the index for this query and still getting the same warning.

The response from query performance:

Parsing JCR-SQL2 statement: explain select [jcr:path], [jcr:score], * from [cq:Workflow] as a where [status] = 'RUNNING' and isdescendantnode(a, '/') order by [startTime] desc
cost using filter Filter(query=explain select [jcr:path], [jcr:score], * from [cq:Workflow] as a where [status] = 'RUNNING' and isdescendantnode(a, '/') order by [startTime] desc, path=//*, property=[status=[RUNNING]])
cost for reference is Infinity
cost for property is Infinity
cost for nodeType is Infinity
Applicable IndexingRule found IndexRule: nt:base
Applicable IndexingRule found IndexRule: cq:Workflow
Applicable IndexingRule found IndexRule: nt:base
Applicable IndexingRule found IndexRule: nt:base
Applicable IndexingRule found IndexRule: nt:base
Applicable IndexingRule found IndexRule: nt:base
cost for [/oak:index/workflowDataLucene] of type (lucene-property) with plan [lucene:workflowDataLucene(/oak:index/workflowDataLucene) status:RUNNING ordering:[{ propertyName : startTime, propertyType : UNDEFINED, order : DESCENDING }]] is 402.00
cost for [/oak:index/ntBaseLucene] of type (lucene-property) with plan [lucene:ntBaseLucene(/oak:index/ntBaseLucene) status:RUNNING sync:(status RUNNING)] is 1408.00
cost for lucene-property is Infinity
cost for aggregate lucene is Infinity
looking for plans for paths : []
cost for aggregate solr is Infinity
cost for traverse is 1063012.0