Uploaded image for project: 'ModeShape'
  1. ModeShape
  2. MODE-2377

QueryManager should use implicit PATH indexes when joining subnodes

    Details

      Description

      The following query returns alls mode:system nodes that have a subsubnode of type nt:propertyDefinition. In a real Query I don't have anything (no property or even a type) to further restrict the search for subsubnode or help the IndexManager to use another index.

      
      select sys.* from [mode:system] as sys\n" +
              "join [nt:nodeType] as ntx on ISDESCENDANTNODE(ntx,sys)\n" +
              "join [nt:propertyDefinition] as pd on ISCHILDNODE(pd,ntx)
      
      

      If I define an explicit nodeType index for mode:system it is used but no implicit PATH index for the subnodes. The plan looks like this:

      AQM -> SELECT sys.[*] FROM [mode:system] AS sys INNER JOIN [nt:nodeType] AS ntx ON ISDESCENDANTNODE(ntx,sys) INNER JOIN [nt:propertyDefinition] AS pd ON ISCHILDNODE(pd,ntx)
       plan -> Project [sys] <PROJECT_COLUMNS=[sys.[jcr:primaryType], sys.[jcr:mixinTypes], sys.[jcr:score], sys.[mode:depth], sys.[jcr:path], sys.[jcr:name], sys.[mode:localName], sys.[mode:id]], PROJECT_COLUMN_TYPES=[STRING, STRING, DOUBLE, LONG, STRING, STRING, STRING, STRING]>
        Join [pd,ntx] <JOIN_TYPE=INNER JOIN, JOIN_ALGORITHM=NESTED_LOOP, JOIN_CONDITION=ISCHILDNODE(pd,ntx)>
          Join [sys,ntx] <JOIN_TYPE=INNER JOIN, JOIN_ALGORITHM=NESTED_LOOP, JOIN_CONDITION=ISDESCENDANTNODE(ntx,sys)>
            Access [sys]
              Project [sys] <PROJECT_COLUMNS=[sys.[jcr:primaryType], sys.[jcr:mixinTypes], sys.[jcr:score], sys.[mode:depth], sys.[jcr:path], sys.[jcr:name], sys.[mode:localName], sys.[mode:id]], PROJECT_COLUMN_TYPES=[STRING, STRING, DOUBLE, LONG, STRING, STRING, STRING, STRING]>
                Select [sys] <SELECT_CRITERIA=sys.[jcr:primaryType] = 'mode:system'>
                  Source [sys] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=sys, 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)]>
                    Index [sys] <INDEX_SPECIFICATION=sysNodes, provider=local, cost~=100, cardinality~=0, selectivity~=NaN, constraints=[sys.[jcr:primaryType] = 'mode:system'], INDEX_USED=true>
            Access [ntx]
              Project [ntx] <PROJECT_COLUMNS=[ntx.[jcr:nodeTypeName], ntx.[jcr:supertypes], ntx.[jcr:isAbstract], ntx.[jcr:isMixin], ntx.[jcr:isQueryable], ntx.[jcr:hasOrderableChildNodes], ntx.[jcr:primaryItemName], ntx.[jcr:primaryType], ntx.[jcr:mixinTypes], ntx.[jcr:score], ntx.[mode:depth], ntx.[jcr:path], ntx.[jcr:name], ntx.[mode:localName], ntx.[mode:id]], PROJECT_COLUMN_TYPES=[STRING, STRING, BOOLEAN, BOOLEAN, BOOLEAN, BOOLEAN, STRING, STRING, STRING, DOUBLE, LONG, STRING, STRING, STRING, STRING]>
                Select [ntx] <SELECT_CRITERIA=ntx.[jcr:primaryType] = 'nt:nodeType'>
                  Source [ntx] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=ntx, 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)]>
          Access [pd]
            Project [pd] <PROJECT_COLUMNS=[pd.[jcr:name], pd.[jcr:autoCreated], pd.[jcr:mandatory], pd.[jcr:isFullTextSearchable], pd.[jcr:isQueryOrderable], pd.[jcr:onParentVersion], pd.[jcr:protected], pd.[jcr:requiredType], pd.[jcr:valueConstraints], pd.[jcr:availableQueryOperators], pd.[jcr:defaultValues], pd.[jcr:multiple], pd.[jcr:primaryType], pd.[jcr:mixinTypes], pd.[jcr:score], pd.[mode:depth], pd.[jcr:path], pd.[mode:localName], pd.[mode:id]], PROJECT_COLUMN_TYPES=[STRING, BOOLEAN, BOOLEAN, BOOLEAN, BOOLEAN, STRING, BOOLEAN, STRING, STRING, STRING, STRING, BOOLEAN, STRING, STRING, DOUBLE, LONG, STRING, STRING, STRING]>
              Select [pd] <SELECT_CRITERIA=pd.[jcr:primaryType] = 'nt:propertyDefinition'>
                Source [pd] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=pd, 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)]>
      
      

      See also https://developer.jboss.org/thread/250314

        Gliffy Diagrams

          Attachments

            Activity

              People

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

                Dates

                • Created:
                  Updated: