oak:index ignored if group.p.or=true and more than one predicates used | Community
Skip to main content
G
grodintierce
June 4, 2024
Solved

oak:index ignored if group.p.or=true and more than one predicates used

  • June 4, 2024
  • 3 replies
  • 769 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by joerghoh

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-and-indexing-best-practices#query-performance-tool

 

 

 

 

3 replies

HrishikeshKagne
Community Advisor
HrishikeshKagneCommunity Advisor
Community Advisor
June 5, 2024

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.

Understanding the Issue

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.

Potential Solutions

  1. 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 }]" }]"/>

 

  1. This index should ensure that the lowercase function is indexed and can be used in queries.

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

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

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

Example Adjusted Query

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

 

Additional Tips

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

 

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

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






Hrishikesh Kagane
joerghoh
Adobe Employee
joerghohAdobe Employee
Adobe Employee
June 5, 2024

what AEM version are you using?

    joerghoh
    Adobe Employee
    joerghohAdobe EmployeeAccepted solution
    Adobe Employee
    June 5, 2024

    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-and-indexing-best-practices#query-performance-tool

     

     

     

     

      Terms & ConditionsAccessibility statement

      Loading footer...