Create new index for a query

[Dave_Hughes]

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?

[Dave_Hughes]

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.

[Sham_HC · Accepted solution]

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. 

[Dave_Hughes]

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.

[Lokesh_Shivalingaiah]

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

[Dave_Hughes]

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])

[smacdonald2008]

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. 

[Dave_Hughes]

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).

[smacdonald2008]

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. 

[Lokesh_Shivalingaiah]

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

[Dave_Hughes]

Thank you.

[Dave_Hughes]

*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 }

[smacdonald2008]

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

[Sham_HC]

enable debug log Or use http://adobe-consulting-services.github.io/acs-aem-tools/explain-query.html

[Dave_Hughes]

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