Hello,
in a repository we have (very large it seems) there are two index
definitions. Please see the image:
https://drive.google.com/file/d/1KS2MZHfj1aRoWm7v6o3kbNFCPormxEft/view?usp=sharing
One index to make the depiction of a content tree faster (Index2, which
indexes nodes of type nt:folder) and one to make queries over a specific
node type and property faster (Index1, which indexes nodes of type
RADICADO_MIGRADO and the property Num_Radicado).
When I use queries like
SELECT [jcr:uuid] FROM [RADICADO_MIGRADO] WHERE [Num_Radicado] =
'R-2022-005778' and isdescendantnode('/')
SELECT [jcr:uuid] FROM [RADICADO_MIGRADO] WHERE [Num_Radicado] =
'R-2022-005778'
Index2 is being picked. Which results in:
The query read or traversed more than 100000 nodes.:
java.lang.UnsupportedOperationException: The query read or traversed more
than 100000 nodes. To avoid affecting other tasks, processing was stopped.
Why is Index2 picked, having that index1 is specific for that node type and
indexes that property? in this case both indexes are returning the same
cost...
Thanks in advance.
P.S.
The cost calculation and chosen plan for each query is here:
Parsing JCR-SQL2 statement: SELECT [jcr:uuid] FROM [RADICADO_MIGRADO]
WHERE [Num_Radicado] = 'R-2022-005778' and isdescendantnode('/')
cost using filter Filter(query=SELECT [jcr:uuid] FROM [RADICADO_MIGRADO]
WHERE [Num_Radicado] = 'R-2022-005778' and isdescendantnode('/'),
path=//*, property=[Num_Radicado=[R-2022-005778]])
cost for reference is Infinity
cost for property is Infinity
cost for nodeType is 409504.0
*cost for [/oak:index/Index2] of type (lucene-property) with plan
[lucene:Index2(/oak:index/Index2) jcr:primaryType:RADICADO_MIGRADO] is
3.00cost for [/oak:index/Index1] of type (lucene-property) with plan
[lucene:Index1(/oak:index/Index1) Num_Radicado:R-2022-005778] is 3.00*
cost for lucene-property is Infinity
cost for aggregate lucene is Infinity
selected index
org.apache.jackrabbit.oak.plugins.index.lucene.LucenePropertyIndex@283b96a
with plan /oak:index/Index2 and
org.apache.jackrabbit.oak.plugins.index.lucene.LucenePropertyIndex@283b96a
with plan /oak:index/Index1 have similar costs 3.0 and 3.0 for query
Filter(query=SELECT [jcr:uuid] FROM [RADICADO_MIGRADO] WHERE
[Num_Radicado] = 'R-2022-005778' and isdescendantnode('/'), path=//*,
property=[Num_Radicado=[R-2022-005778]]) - check query explanation / index
definitions
cost for traverse is 3823716.0
count: 1 query: SELECT [jcr:uuid] FROM [RADICADO_MIGRADO] WHERE
[Num_Radicado] = 'x' and isdescendantnode('x')
query execute SELECT [jcr:uuid] FROM [RADICADO_MIGRADO] WHERE
[Num_Radicado] = 'R-2022-005778' and isdescendantnode('/')
query plan [RADICADO_MIGRADO] as [RADICADO_MIGRADO] /*
lucene:Index2(/oak:index/Index2) jcr:primaryType:RADICADO_MIGRADO where
([RADICADO_MIGRADO].[Num_Radicado] = 'R-2022-005778') and
(isdescendantnode([RADICADO_MIGRADO], [/])) */
The query read or traversed more than 100000 nodes.
Parsing JCR-SQL2 statement: SELECT [jcr:uuid] FROM [RADICADO_MIGRADO]
WHERE [Num_Radicado] = 'R-2022-005778'
cost using filter Filter(query=SELECT [jcr:uuid] FROM [RADICADO_MIGRADO]
WHERE [Num_Radicado] = 'R-2022-005778', path=*,
property=[Num_Radicado=[R-2022-005778]])
cost for reference is Infinity
cost for property is Infinity
cost for nodeType is 409504.0
*cost for [/oak:index/Index2] of type (lucene-property) with plan
[lucene:Index2(/oak:index/Index2) jcr:primaryType:RADICADO_MIGRADO] is
3.00cost for [/oak:index/Index1] of type (lucene-property) with plan
[lucene:Index1(/oak:index/Index1) Num_Radicado:R-2022-005778] is 3.00*
cost for lucene-property is Infinity
cost for aggregate lucene is Infinity
selected index
org.apache.jackrabbit.oak.plugins.index.lucene.LucenePropertyIndex@1cdf077b
with plan /oak:index/Index2 and
org.apache.jackrabbit.oak.plugins.index.lucene.LucenePropertyIndex@1cdf077b
with plan /oak:index/Index1 have similar costs 3.0 and 3.0 for query
Filter(query=SELECT [jcr:uuid] FROM [RADICADO_MIGRADO] WHERE
[Num_Radicado] = 'R-2022-005778', path=*,
property=[Num_Radicado=[R-2022-005778]]) - check query explanation / index
definitions
cost for traverse is 3889252.0
count: 1 query: SELECT [jcr:uuid] FROM [RADICADO_MIGRADO] WHERE
[Num_Radicado] = 'x'
query execute SELECT [jcr:uuid] FROM [RADICADO_MIGRADO] WHERE
[Num_Radicado] = 'R-2022-005778'
query plan [RADICADO_MIGRADO] as [RADICADO_MIGRADO] /*
lucene:Index2(/oak:index/Index2) jcr:primaryType:RADICADO_MIGRADO where
[RADICADO_MIGRADO].[Num_Radicado] = 'R-2022-005778' */
The query read or traversed more than 100000 nodes.
