Details
-
Bug
-
Resolution: Done
-
Major
-
4.0.0.Final
-
None
Description
The following two plans are more or less the same, only difference is that in the second case there is no node matching the criteria.
The plan of the second query shows that an implicit path index is used instead of the more specialized index.
This is leading to a 30 - 50 ms delay when trying to access the iterator (I guess because all 10k nodes of the path index have to be matched?). Node present: 1ms, node not present: 30ms access time.
Access [BASE] Project [BASE] <PROJECT_COLUMNS=[BASE.[jcr:uuid]], PROJECT_COLUMN_TYPES=[STRING]> Select [BASE] <SELECT_CRITERIA=BASE.rvgrpnr = '1014563'> Select [BASE] <SELECT_CRITERIA=ISDESCENDANTNODE(BASE,'/data/stand-2014-04[21]')> Select [BASE] <SELECT_CRITERIA=BASE.[jcr:primaryType] = 'nt:ag'> Source [BASE] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=BASE, SOURCE_COLUMNS=[jcr:frozenUuid(STRING), mode:sharedUuid(STRING), mode:nodeTypeName(STRING), jcr:defaultValues(STRING), mode:projectedNodeKey(STRING), jcr:mixinTypes(STRING), jcr:frozenPrimaryType(STRING), jcr:defaultPrimaryType(STRING), jcr:statement(STRING), jcr:lastModifiedBy(STRING), jcr:mimeType(STRING), jcr:hasOrderableChildNodes(BOOLEAN), jcr:etag(STRING), jcr:encoding(STRING), jcr:root(STRING), mode:kind(STRING), jcr:supertypes(STRING), jcr:successors(STRING), jcr:primaryItemName(STRING), jcr:hold(STRING), jcr:workspace(STRING), jcr:isDeep(BOOLEAN), jcr:primaryType(STRING), mode:externalNodeKey(STRING), mode:derivedFrom(STRING), mode:isHeldBySession(BOOLEAN), jcr:baseVersion(STRING), jcr:lastModified(DATE), jcr:mergeFailed(STRING), mode:derivedAt(DATE), jcr:requiredPrimaryTypes(STRING), jcr:multiple(BOOLEAN), mode:generated(BOOLEAN), jcr:activityTitle(STRING), jcr:lifecyclePolicy(STRING), jcr:isMixin(BOOLEAN), jcr:availableQueryOperators(STRING), jcr:childVersionHistory(STRING), jcr:content(STRING), jcr:autoCreated(BOOLEAN), mode:synchronous(BOOLEAN), name(STRING), mode:alias(STRING), jcr:createdBy(STRING), jcr:isFullTextSearchable(BOOLEAN), jcr:uuid(STRING), jcr:onParentVersion(STRING), mode:expirationDate(DATE), jcr:copiedFrom(STRING), mode:lockToken(STRING), jcr:title(STRING), jcr:isAbstract(BOOLEAN), jcr:versionableUuid(STRING), jcr:versionHistory(STRING), jcr:predecessors(STRING), jcr:lockIsDeep(BOOLEAN), jcr:lockOwner(STRING), mode:sha1(STRING), jcr:repository(STRING), mode:columnTypeName(STRING), jcr:created(DATE), jcr:frozenMixinTypes(STRING), jcr:description(STRING), jcr:text(STRING), jcr:host(STRING), jcr:configuration(STRING), jcr:port(STRING), mode:workspace(STRING), jcr:nodeTypeName(STRING), jcr:isQueryable(BOOLEAN), jcr:data(BINARY), jcr:language(STRING), jcr:isQueryOrderable(BOOLEAN), jcr:isCheckedOut(BOOLEAN), jcr:mandatory(BOOLEAN), jcr:protected(BOOLEAN), jcr:protocol(STRING), jcr:sameNameSiblings(BOOLEAN), jcr:requiredType(STRING), mode:lockingSession(STRING), jcr:messageId(STRING), jcr:id(STRING), mode:uri(STRING), mode:propertyName(STRING), mode:workspaces(STRING), privileges(STRING), mode:isSessionScoped(BOOLEAN), jcr:valueConstraints(STRING), jcr:retentionPolicy(STRING), jcr:activity(STRING), jcr:currentLifecycleState(STRING), jcr:score(DOUBLE), mode:depth(LONG), jcr:path(STRING), jcr:name(STRING), mode:localName(STRING), mode:id(STRING), jcr:uuid(STRING)]> Index [BASE] <INDEX_SPECIFICATION=SI_Ag_rvgrpnr, provider=local, cost~=100, cardinality~=8, selectivity~=1.2596838E-4, constraints=[BASE.rvgrpnr = '1014563'], INDEX_USED=true> Index [BASE] <INDEX_SPECIFICATION=DescendantsByPath, provider=<implicit>, cost~=1000, cardinality~=10000, selectivity~=?, constraints=[ISDESCENDANTNODE(BASE,'/data/stand-2014-04[21]')], path=/data/stand-2014-04[21]>
Access [BASE] Project [BASE] <PROJECT_COLUMNS=[BASE.[jcr:uuid]], PROJECT_COLUMN_TYPES=[STRING]> Select [BASE] <SELECT_CRITERIA=BASE.rvnr = '4606564'> Select [BASE] <SELECT_CRITERIA=ISDESCENDANTNODE(BASE,'/data/stand-2014-04[21]')> Select [BASE] <SELECT_CRITERIA=BASE.[jcr:primaryType] = 'nt:ai'> Source [BASE] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=BASE, SOURCE_COLUMNS=[jcr:frozenUuid(STRING), mode:sharedUuid(STRING), mode:nodeTypeName(STRING), jcr:defaultValues(STRING), mode:projectedNodeKey(STRING), jcr:mixinTypes(STRING), jcr:frozenPrimaryType(STRING), jcr:defaultPrimaryType(STRING), jcr:statement(STRING), jcr:lastModifiedBy(STRING), jcr:mimeType(STRING), jcr:hasOrderableChildNodes(BOOLEAN), jcr:etag(STRING), jcr:encoding(STRING), jcr:root(STRING), mode:kind(STRING), jcr:supertypes(STRING), jcr:successors(STRING), jcr:primaryItemName(STRING), jcr:hold(STRING), jcr:workspace(STRING), jcr:isDeep(BOOLEAN), jcr:primaryType(STRING), mode:externalNodeKey(STRING), mode:derivedFrom(STRING), mode:isHeldBySession(BOOLEAN), jcr:baseVersion(STRING), jcr:lastModified(DATE), jcr:mergeFailed(STRING), mode:derivedAt(DATE), jcr:requiredPrimaryTypes(STRING), jcr:multiple(BOOLEAN), mode:generated(BOOLEAN), jcr:activityTitle(STRING), jcr:lifecyclePolicy(STRING), jcr:isMixin(BOOLEAN), jcr:availableQueryOperators(STRING), jcr:childVersionHistory(STRING), jcr:content(STRING), jcr:autoCreated(BOOLEAN), mode:synchronous(BOOLEAN), name(STRING), mode:alias(STRING), jcr:createdBy(STRING), jcr:isFullTextSearchable(BOOLEAN), jcr:uuid(STRING), jcr:onParentVersion(STRING), mode:expirationDate(DATE), jcr:copiedFrom(STRING), mode:lockToken(STRING), jcr:title(STRING), jcr:isAbstract(BOOLEAN), jcr:versionableUuid(STRING), jcr:versionHistory(STRING), jcr:predecessors(STRING), jcr:lockIsDeep(BOOLEAN), jcr:lockOwner(STRING), mode:sha1(STRING), jcr:repository(STRING), mode:columnTypeName(STRING), jcr:created(DATE), jcr:frozenMixinTypes(STRING), jcr:description(STRING), jcr:text(STRING), jcr:host(STRING), jcr:configuration(STRING), jcr:port(STRING), mode:workspace(STRING), jcr:nodeTypeName(STRING), jcr:isQueryable(BOOLEAN), jcr:data(BINARY), jcr:language(STRING), jcr:isQueryOrderable(BOOLEAN), jcr:isCheckedOut(BOOLEAN), jcr:mandatory(BOOLEAN), jcr:protected(BOOLEAN), jcr:protocol(STRING), jcr:sameNameSiblings(BOOLEAN), jcr:requiredType(STRING), mode:lockingSession(STRING), jcr:messageId(STRING), jcr:id(STRING), mode:uri(STRING), mode:propertyName(STRING), mode:workspaces(STRING), privileges(STRING), mode:isSessionScoped(BOOLEAN), jcr:valueConstraints(STRING), jcr:retentionPolicy(STRING), jcr:activity(STRING), jcr:currentLifecycleState(STRING), jcr:score(DOUBLE), mode:depth(LONG), jcr:path(STRING), jcr:name(STRING), mode:localName(STRING), mode:id(STRING), jcr:uuid(STRING)]> Index [BASE] <INDEX_SPECIFICATION=DescendantsByPath, provider=<implicit>, cost~=1000, cardinality~=10000, selectivity~=?, constraints=[ISDESCENDANTNODE(BASE,'/data/stand-2014-04[21]')], path=/data/stand-2014-04[21], INDEX_USED=true> Index [BASE] <INDEX_SPECIFICATION=SI_Ai_rvnr, provider=local, cost~=100, cardinality~=0, selectivity~=0.0, constraints=[BASE.rvnr = '4606564']>
What also is VERY misleading is that the cardinality of the second index of the plan is logged to be 0, it seems to always be the number of results for that index, not the total number of elements in the index as one (or at least myself) would expect.