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

[grodintierce]

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?

[HrishikeshKa]

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.

[Jörg_Hoh]

what AEM version are you using?

[Jörg_Hoh · Accepted solution]

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