Query issues on Cloud environment during keyset pagination implementation
I have implemented keyset pagination and my query looks like this:
SELECT [jcr:content/metadata/finishingMaterial] FROM [sling:Folder] AS folder WHERE [jcr:content/metadata/finishingMaterial] is not null and [jcr:content/metadata/finishingMaterial] > 'namespace:finishing-material/fabric/15431/15397' and isdescendantnode(folder, '/content/dam/namespace') order by [jcr:content/metadata/finishingMaterial]
This is how the index for it looks like :
<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:jcr="http://www.jcp.org/jcr/1.0"
xmlns:nt="http://www.jcp.org/jcr/nt/1.0"
xmlns:oak="http://jackrabbit.apache.org/oak/ns/1.0"
xmlns:sling="http://sling.apache.org/jcr/sling/1.0"
jcr:primaryType="oak:QueryIndexDefinition"
type="lucene"
async="[async,nrt]"
compatVersion="{Long}2"
evaluatePathRestrictions="{Boolean}true"
queryPaths="[/content/dam/namespace]"
includedPaths="[/content/dam/namespace]">
<aggregates jcr:primaryType="nt:unstructured">
<sling:Folder jcr:primaryType="nt:unstructured">
<include0
jcr:primaryType="nt:unstructured"
path="jcr:content/metadata"/>
</sling:Folder>
</aggregates>
<indexRules jcr:primaryType="nt:unstructured">
<sling:Folder jcr:primaryType="nt:unstructured">
<properties jcr:primaryType="nt:unstructured">
<finishingMaterial
jcr:primaryType="nt:unstructured"
name="jcr:content/metadata/finishingMaterial"
propertyIndex="{Boolean}true"
notNullCheckEnabled="{Boolean}true"
ordered="{Boolean}true"/>
</properties>
</sling:Folder>
</indexRules>
</jcr:root>
The query runs fine locally for around 20500 estimated items.
On the cloud environment with the similar(if not the same) dataset the query is very slow but only the first time you execute it.
This only happens the first time you invoke this query. Each subsequent attempt is fast. Any ideas what could cause this?
