I am using a query-builder query plus an index on our products to speed up the query on nodenames.
It must work case insensitive. My index is using a function "fn:lower-case(fn:name())".
The query is as seen below.
It is fast, returing the expected result in 10ms. While querying over 50k nodes.
type=dam:Asset
path=/content/dam/ourProducts/powerFruits
p.limit=-1
nodename=pineApple //no need to lower-case "pineApple", querybuilder predicate is taking care of it
nodename.case=ignore
But as soon as I query for multiple nodenames, using a group, the index is ignored.
The result is as expected, however the response time is over a second.
Query with the group seen below:
type=dam:Asset
path=/content/dam/ourProducts/powerFruits
p.limit=-1
group.1_nodename=pineApple //no need to lower-case "pineApple", querybuilder predicate is taking care of it
group.1_nodename.case=ignore
group.2_nodename=cheRry //no need to lower-case "cheRry", querybuilder predicate is taking care of it
group.2_nodename.case=ignore
group.p.or=true
I also tried alternative queries using xpath:
type=dam:Asset
path=/content/dam/ourProducts/powerFruits
p.limit=-1
xpath=(fn:lower-case(fn:name())='pineapple' or fn:lower-case(fn:name())='cherry') //must lower-case "pineApple" or "cheRry" here in application code
Or grouping xpaths, no success of improving the response time:
type=dam:Asset
path=/content/dam/ourProducts/powerFruits
p.limit=-1
group.1_xpath=fn:lower-case(fn:name())='pineapple' //must lower-case "pineApple" here in application code
group.2_xpath=fn:lower-case(fn:name())='cherry' //must lower-case "cheRry" here in application code
group.p.or=true
Am I doing something wrong or don't understand how grouping works?
Solved! Go to Solution.
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
p.limit=-1 asks the query engine to return all results. And querybuilder will read the complete result set, which can take time because all results need to be filtered.
Can you please run the query through the query explain tool? In AEM CS you can also use the query performance tool, see https://experienceleague.adobe.com/en/docs/experience-manager-cloud-service/content/operations/query...
Hi @grodintierce ,
When using the oak:index with queries involving multiple predicates in a group, especially with group.p.or=true, it's common to face issues where the index is not utilized as expected. This can lead to performance degradation, as the query might fall back to a full scan instead of leveraging the index.
The problem arises because the query optimizer might not correctly handle the combined OR predicates with the functional index you've defined. When multiple predicates are used, the optimizer might not see how to apply the index efficiently, especially with the case-insensitive matching requirement.
Review and Optimize Index Definition: Ensure that your index is well-defined and supports the use case of querying with multiple predicates. Here is a refined example of how the index should look:
<oak:QueryIndexDefinition jcr:primaryType="oak:QueryIndexDefinition"
type="lucene"
async="async"
compatVersion="2"
includeNodeTypes="[dam:Asset]"
indexRules="[{
nodeType="nt:base",
properties="[{
name="fn:lower-case(fn:name())",
propertyIndex=true,
ordered=false
}]"
}]"/>
This index should ensure that the lowercase function is indexed and can be used in queries.
Use Combined Functional Index: If the functional index on fn:lower-case(fn:name()) is not working well with group.p.or, consider creating a custom index or combined property index that might better handle multiple predicates.
Leverage a Full-Text Index: If the product names are relatively distinct and performance is a critical issue, you might consider using a full-text index instead of a specific property index. This can sometimes offer better performance for complex queries involving multiple conditions.
Refine Query Structure: Given the issue with group.p.or, you might need to adjust your query to better leverage the index. This might involve simplifying the query structure or using a different approach to combine the predicates.
Here’s an alternative approach using a simplified query structure:
type=dam:Asset
path=/content/dam/ourProducts/powerFruits
p.limit=-1
property=[jcr:content/metadata/dc:title]
property.operation=like
property.value=*(pineapple|cherry)*
Test with Explain Plan: Use the explain query option to see how the query is being executed and whether the index is being used. This can provide insights into why the optimizer is not using the index as expected.
explain select * from [dam:Asset] as s where isdescendantnode(s, '/content/dam/ourProducts/powerFruits') and (fn:lower-case(fn:name())='pineapple' or fn:lower-case(fn:name())='cherry')
Optimize Query Execution: Ensure that the queries are executed in a way that the index can be utilized effectively. Sometimes, restructuring the query or breaking it into multiple parts can help.
Use Oak Indexing Tools: Utilize tools provided by Oak to debug and analyze index usage. The Oak index management tools can help identify issues with the index configuration and usage.
The key to solving the performance issue with multiple predicates and index usage lies in refining the index definition, adjusting the query structure, and leveraging tools to analyze and optimize the query execution. By taking these steps, you can ensure that the query builder utilizes the index effectively, even with multiple predicates.
Views
Replies
Total Likes
what AEM version are you using?
p.limit=-1 asks the query engine to return all results. And querybuilder will read the complete result set, which can take time because all results need to be filtered.
Can you please run the query through the query explain tool? In AEM CS you can also use the query performance tool, see https://experienceleague.adobe.com/en/docs/experience-manager-cloud-service/content/operations/query...
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies