Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 4.0.0.Final
    • Fix Version/s: 4.1.0.Final
    • Component/s: Query
    • Labels:
      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.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                hchiorean Horia Chiorean
                Reporter:
                bes82 Bjoern Schmidt
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: