Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

Create new index for a query

Avatar

Former Community Member

I am trying to create a new index for a slow query.  I've tried many variations of an oak:QueryIndexDefinition (tried both property and lucene types), but none seem to apply for this query.  Please help me understand this.

 

From the ACS Explain Query Tool:

{ "statement": "SELECT [fileReference] FROM [nt:unstructured] WHERE ISSAMENODE([/content/demo-spark/jcr:content/bgimage]) OR ISSAMENODE([/content/demo-spark/fr_FR/jcr:content/bgimage])", "language": "JCR-SQL2", "explain": { "logs": [ "cost using filter Filter(query=explain SELECT [fileReference] FROM [nt:unstructured] WHERE ISSAMENODE([/content/demo-spark/jcr:content/bgimage]) OR ISSAMENODE([/content/demo-spark/fr_FR/jcr:content/bgimage]), path=*)", "cost for aggregate lucene is Infinity", "Evaluating plan with index definition Lucene Index : /oak:index/damAssetLucene", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "Evaluating plan with index definition Lucene Index : authorizables(/oak:index/authorizables)", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "Evaluating plan with index definition Lucene Index : /oak:index/lucene", "Index is old format. Not supported", "Evaluating plan with index definition Lucene Index : cq:Page(/oak:index/cqPageLucene)", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "Evaluating plan with index definition Lucene Index : tags(/oak:index/ntBaseLucene)", "Applicable IndexingRule found IndexRule: nt:base", "Evaluating plan with index definition Lucene Index : cqTag(/oak:index/cqTagLucene)", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "Evaluating plan with index definition Lucene Index : workflow(/oak:index/workflowDataLucene)", "No applicable IndexingRule found for any of the superTypes [nt:unstructured, nt:base]", "cost for lucene-property is Infinity", "cost for reference is Infinity", "getPlans(Filter, List<OrderEntry>, NodeState)", "getPlans() - filter: Filter(query=explain SELECT [fileReference] FROM [nt:unstructured] WHERE ISSAMENODE([/content/demo-spark/jcr:content/bgimage]) OR ISSAMENODE([/content/demo-spark/fr_FR/jcr:content/bgimage]), path=*) - ", "getPlans() - sortOrder: null - ", "getPlans() - rootState: { jcr:primaryType = rep:root, jcr:mixinTypes = [rep:AccessControllable, rep:RepoAccessControllable], sling:target = /index.html, :childOrder = [bin, rep:repoPolicy, rep:policy, jcr:system, var, libs, etc, home, content, favicon.ico, system, apps, conf, tmp], sling:resourceType = sling:redirect, :async : { async-temp = [9995c2a6-3940-4e31-a8a4-5f0f4e11ced0, 40f256b6-a463-41e9-bdc7-8a1d5187da61], async = 9995c2a6-3940-4e31-a8a4-5f0f4e11ced0, async-LastIndexedTo = 2015-08-20T15:19:07.578Z }, tmp = { ... }, home = { ... }, favicon.ico = { ... }, :clusterConfig : { :clusterId = 101ee51d-0352-4933-96d5-e372aed2c867 }, oak:index = { ... }, conf = { ... }, jcr:system = { ... }, etc = { ... }, system = { ... }, content = { ... }, bin : { jcr:primaryType = nt:folder, jcr:mixinTypes = [mix:versionable], jcr:createdBy = admin, jcr:versionHistory = b636ebc7-cf9a-430f-a276-10706b37f8c4, jcr:predecessors = [], jcr:created = 2014-04-02T21:40:10.723Z, jcr:baseVersion = c3735025-3e64-4d0e-b132-9dc08d0193c1, jcr:isCheckedOut = false, jcr:uuid = 40ed38c9-cb13-41dc-8602-0249e2c1b4c2 }, rep:policy = { ... }, libs = { ... }, rep:repoPolicy = { ... }, var = { ... }, apps = { ... } } - ", "cost for ordered is Infinity", "cost for nodeType is Infinity", "cost for property is Infinity", "cost for traverse is 1303100.0" ], "plan": "[nt:unstructured] as [nt:unstructured] /* traverse \"*\" */", "traversal": true, "slow": true } }

 

/content/demo-spark/oak:index/fileReferenceLucene.-1.json:
(I've also tried creating this as a root index; /oak:index/fileReferenceLucene)

{ "jcr:primaryType": "oak:QueryIndexDefinition", "compatVersion": 2, "name": "nt:base/fileReference", "type": "lucene", "async": "async", "reindex": false, "reindexCount": 7, "indexRules": { "jcr:primaryType": "nt:unstructured", "nt:base": { "jcr:primaryType": "nt:unstructured", "includePropertyTypes": "all", "properties": { "jcr:primaryType": "nt:unstructured", "fileReference": { "jcr:primaryType": "nt:unstructured", "propertyIndex": true, "name": "fileReference" } } } } }

 

Is there something that I'm missing?  Why can I not get this index to apply against the slow query?

1 Accepted Solution

Avatar

Correct answer by
Level 10

Hmmm..  Sorry missed the description.   From Explain query output your index not even listed that means your custom nt:base rule would over shadow the default nt:base rule.   Looking into your query & based on path "/content/demo-spark/jcr:content/bgimage"  I am assuming "demo-spark" is cq:Page. So modify your index defnition to node type CQ:Page OR made if you modify the OOB one to include your defnition. 

View solution in original post

14 Replies

Avatar

Former Community Member
Sham, please re-read the question.  We have used the Explain Query Tool, its output is included in the question. Debug logging is turned on, but we're not seeing anything useful.

Avatar

Correct answer by
Level 10

Hmmm..  Sorry missed the description.   From Explain query output your index not even listed that means your custom nt:base rule would over shadow the default nt:base rule.   Looking into your query & based on path "/content/demo-spark/jcr:content/bgimage"  I am assuming "demo-spark" is cq:Page. So modify your index defnition to node type CQ:Page OR made if you modify the OOB one to include your defnition. 

Avatar

Former Community Member

Sham, thanks, but I don't quite follow.

You are correct in assuming that demo-spark is a cq:Page.  Because of this, shouldn't our fileReference property already be indexed as part of the cqPageLucene index (due to its aggregates)?

You mentioned "From Explain query output your index not even listed that means your custom nt:base rule would overshadow the default nt:base rule".  That does not make sense to me.  If my new index is not listed, I assume that it has not been detected (for this query).  If this is the case, how would it overshadow a different index?  Should all the indexes be maintained, and only the best one used at query time?

Forgetting about the Lucene index for a moment, I also mentioned that I tried creating a simple property-type index.  This type of index did not specify the types that it applied to.  I suspect this would not cause the overshadowing that you mentioned, but again, we did not see the custom index being used.

Avatar

Level 10

is this your full query ?? if not can you share your actual query

Avatar

Former Community Member

Yes...  the query is as above.

SELECT [fileReference] FROM [nt:unstructured] WHERE ISSAMENODE([/content/demo-spark/jcr:content/bgimage]) OR ISSAMENODE([/content/demo-spark/fr_FR/jcr:content/bgimage])

Avatar

Level 10

Here is good OAK docs on this subject: 

http://jackrabbit.apache.org/oak/docs/query/lucene.html

Moving forward - we are going to give this area some attention in the community. We will be doing some articles and base an AEM Ask the Community Experts on this topic. 

Avatar

Former Community Member

Thanks Scott, but following that document is what got me into the current situation.

If you compare my index definition (above) to the Oak documentation, the definition appears to be compliant.  It does not, however, seem to be working.  This is why I have posted, I'm trying to understand why my index definition is not being used for the query (above).

Avatar

Level 10

I will pass this to our OAK experts. Like i mentioned in the previous comment- our community members are going to give this area more attention and have our own working examples. You will soon see working examples come out. For now - i have passed this to some of our experts. 

Avatar

Level 10

It should pick as it does for me on my test. Can you file a day care support ticket so that they can look at your code and analyse the same

Avatar

Former Community Member

*bump*

We're still looking for help.  Can anybody explain why the created index wasn't working?

 

I've also tried creating a 'property' index:

{ "jcr:primaryType": "oak:QueryIndexDefinition", "propertyNames": ["fileReference"], "type": "property", "reindex": false, "reindexCount": 1 }

Avatar

Level 10

YOu are welcome - we know this is an area where community members like you need more working examples and content. 

Avatar

Former Community Member

Thanks, bsloki.  In what way are you verifying that the index has been used?