We have a tree with the following structure nt:folder/nt:folder/nt:folder/nbe:document  where nbe:document is extended nt:file type.

We search and show nbe:document  in paginated list with a limit parameter set to 20. The query does return only 20 items, but in modeshape trace logs I see that ALL nodes matching the criteria are materialized. Eg. Query matches 24 files, so 24 a materialized but only 20 returned. The issue came up when query matched 12,000 files (testing) but I care of only the first 20 to display in a first page.

So question - how to avoid materializing all nodes?

In logs I see the following lucene statement: 

[org.modeshape.jcr.query.lucene.LuceneQueryEngine] Executing the lucene query: +(::wks:system ::wks:default) +::pth:/accounts[1]/1111[1]/documents[1]/* +period:.*2013.* +journal:CB +jcr:primaryType:nbe:document

[nbe:document] > nt:file

- scannDate (DATE) mandatory COPY

- period (STRING) mandatory COPY

- journal (STRING) mandatory COPY

- status (STRING) mandatory COPY

- rejectionReason (STRING) COPY

- originalFilename (STRING) COPY

- originalMD5 (STRING) COPY

- pieces (REFERENCE) multiple COPY

Here is a JCR-SQL2 query: SELECT * from [nbe:document] WHERE PATH([nbe:document]) LIKE '/accounts/111/documents/%' ORDER BY [jcr:name] LIMIT 20 OFFSET 0

Standalone.xml configuration:

<subsystem xmlns="urn:jboss:domain:infinispan:1.3">

     <cache-container name="hibernate" default-cache="local-query" module="org.jboss.as.jpa.hibernate:4">

          <local-cache name="entity">

               <transaction mode="NON_XA"/>

               <eviction strategy="LRU" max-entries="10000"/>

               <expiration max-idle="100000"/>

          </local-cache>

          <local-cache name="local-query">

               <transaction mode="NONE"/>

               <eviction strategy="LRU" max-entries="10000"/>

               <expiration max-idle="100000"/>

          </local-cache>

          <local-cache name="timestamps">

               <transaction mode="NONE"/>

               <eviction strategy="NONE"/>

          </local-cache>

     </cache-container>

     <cache-container name="modeshape">

          <local-cache name="nbeportal">

               <transaction mode="NON_XA"/>

               <eviction strategy="LRU" max-entries="1000"/>

               <file-store relative-to="jboss.server.data.dir" path="modeshape/store/nbeportal" passivation="false" purge="false"/>

          </local-cache>

     </cache-container>

</subsystem>

<subsystem xmlns="urn:jboss:domain:modeshape:1.0">

     <repository name="nbeportal" cache-name="nbeportal" cache-container="modeshape">

          <indexing rebuild-upon-startup="NEVER"/>

     </repository>

</subsystem>