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?
Solved! Go to Solution.
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
is this your full query ?? if not can you share your actual query
Views
Replies
Total Likes
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])
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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).
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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
Views
Replies
Total Likes
Thank you.
Views
Replies
Total Likes
*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 }
Views
Replies
Total Likes
YOu are welcome - we know this is an area where community members like you need more working examples and content.
Views
Replies
Total Likes
enable debug log Or use http://adobe-consulting-services.github.io/acs-aem-tools/explain-query.html
Views
Replies
Total Likes
Thanks, bsloki. In what way are you verifying that the index has been used?
Views
Replies
Total Likes