5 Replies Latest reply on Oct 31, 2018 3:13 AM by Gerhard Stummer

    bad performance on my JCR queries with LocalIndexProvider after update 4.3.0.Final to 5.4.1.Final

    Gerhard Stummer Newbie

      Hi,

       

      after updating from Modeshape 4.3.0 to 5.4.1 I have bad performance on my JCR queries with LocalIndexProvider:

       

      here my settings

       

         "indexProviders" : {
              "local" : {
                  "classname" : "org.modeshape.jcr.index.local.LocalIndexProvider",
                  "relativeTo" : "${modeshape.index.storage.location:/data/intranet/index}",
                  "path" : "local",
                  "cacheSize" : "524288",
                  "cacheLRUEnable" : "true",
                  "mmapFileEnable" : "true",
                  "asyncWrite" : true,
                  "transactionDisable" : true,
                  "commitFileSyncDisable" : "false"
              }
          },
          "indexes" : {
              "primaryTypes" : {
                  "kind" : "value",
                  "provider" : "local",
                  "synchronous" : true,
                  "nodeType" : "nt:base",
                  "columns" : "jcr:primaryType(NAME)",
                  "description" : "Index for quickly identifying nodes by node type"
              },
              "uuids" : {
                  "kind" : "unique",
                  "provider" : "local",
                  "synchronous" : true,
                  "nodeType" : "intranet:base",
                  "columns" : "jcr:uuid(STRING)",
                  "description" : "index to query for id's"
              },
              "workflowState" : {
                  "kind" : "enumerated",
                  "provider" : "local",
                  "synchronous" : true,
                  "nodeType" : "intranet:baseContent",
                  "columns" : "workflowState(DECIMAL)",
                  "description" : "index for querying workflowState column"
              },
              "tupleValue1" : {
                  "kind" : "value",
                  "provider" : "local",
                  "synchronous" : true,
                  "nodeType" : "intranet:tuple",
                  "columns" : "value1(UNDEFINED)",
                  "description" : "custom index for tuple values"
              },
              "tupleValue2" : {
                  "kind" : "value",
                  "provider" : "local",
                  "synchronous" : true,
                  "nodeType" : "intranet:tuple",
                  "columns" : "value2(UNDEFINED)",
                  "description" : "custom index for tuple values"
              },
      ...

       


      The Query:

       

      select content.[jcr:uuid] from [intranet:article] as content 
         join [intranet:list] as list on ISCHILDNODE(list, content) 
         join [intranet:tuple] as tuple on ISCHILDNODE(tuple, list) 
         where content.workflowState = 3 and tuple.value1 = 17


      Execution Time in 4.3.0: max. 10-15 seconds

      Execution Time in 5.4.1: more than 30 minutes

       

      The execution plan in both versions for the query seems to be the same (here from 5.4.1):

       

      {
          "statement": "select content.[jcr:uuid] from [intranet:article] as content \r\njoin [intranet:list] as list on ISCHILDNODE(list, content) \r\njoin [intranet:tuple] as tuple on ISCHILDNODE(tuple, list) \r\nwhere content.workflowState = 3\r\nand tuple.value1 = 17\r\n\r\n",
          "language": "JCR-SQL2",
          "abstractQueryModel": "SELECT content.[jcr:uuid] FROM [intranet:article] AS content INNER JOIN [intranet:list] AS list ON ISCHILDNODE(list,content) INNER JOIN [intranet:tuple] AS tuple ON ISCHILDNODE(tuple,list) WHERE (content.workflowState = CAST('3' AS LONG) AND tuple.value1 = CAST('17' AS LONG))",
          "queryPlan": [
              "Project [content] <PROJECT_COLUMNS=[content.[jcr:uuid]], PROJECT_COLUMN_TYPES=[STRING]>",
              "  Join [tuple,list] <JOIN_TYPE=INNER JOIN, JOIN_ALGORITHM=NESTED_LOOP, JOIN_CONDITION=ISCHILDNODE(tuple,list)>",
              "    Join [list,content] <JOIN_TYPE=INNER JOIN, JOIN_ALGORITHM=NESTED_LOOP, JOIN_CONDITION=ISCHILDNODE(list,content)>",
              "      Access [content]",
              "        Project [content] <PROJECT_COLUMNS=[content.content, content.editorialTeam, content.additionalEditorialTeams, content.shared, content.sharedByBankSector, content.sharedForBankSectors, content.importedByBankSectors, content.latestLiveVersion, content.changeComment, content.workingCopy, content.liveVersion, content.basedOnVersion, content.title, content.description, content.validFrom, content.validTo, content.workflowState, content.owner, content.ownerBankSector, content.[jcr:versionHistory], content.[jcr:baseVersion], content.[jcr:predecessors], content.[jcr:mergeFailed], content.[jcr:activity], content.[jcr:configuration], content.archivedAt, content.archivedBy, content.modifiedAt, content.modifiedBy, content.reviewedAt, content.reviewedBy, content.[jcr:created], content.[jcr:createdBy], content.[jcr:lastModified], content.[jcr:lastModifiedBy], content.[jcr:isCheckedOut], content.[jcr:uuid], content.[jcr:primaryType], content.[jcr:mixinTypes], content.[mode:localName], content.[mode:id], content.[mode:depth], content.[jcr:score], content.[jcr:path], content.[jcr:name]], PROJECT_COLUMN_TYPES=[STRING, LONG, LONG, BOOLEAN, DECIMAL, STRING, DECIMAL, STRING, STRING, STRING, STRING, STRING, STRING, STRING, DATE, DATE, DECIMAL, DECIMAL, DECIMAL, STRING, STRING, STRING, STRING, STRING, STRING, DATE, STRING, DATE, STRING, DATE, STRING, DATE, STRING, DATE, STRING, BOOLEAN, STRING, STRING, STRING, STRING, STRING, LONG, DOUBLE, STRING, STRING]>",
              "          Select [content] <SELECT_CRITERIA=content.workflowState = CAST('3' AS LONG)>",
              "            Select [content] <SELECT_CRITERIA=content.[jcr:primaryType] = 'intranet:article'>",
              "              Source [content] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=content, SOURCE_COLUMNS=[changeComment(STRING), reviewedAt(DATE), jcr:isCheckedOut(BOOLEAN), jcr:defaultPrimaryType(STRING), mode:uri(STRING), newsValidTo(DATE), mode:lockingSession(STRING), jcr:etag(STRING), archivedAt(DATE), jcr:created(DATE), reviewedBy(STRING), jcr:sameNameSiblings(BOOLEAN), jcr:encoding(STRING), jcr:workspace(STRING), workflowState(DECIMAL), jcr:activityTitle(STRING), jcr:mergeFailed(STRING), jcr:frozenMixinTypes(STRING), jcr:isQueryOrderable(BOOLEAN), type(DECIMAL), jcr:lastModifiedBy(STRING), bytes(BINARY), jcr:childVersionHistory(STRING), mode:synchronous(BOOLEAN), jcr:repository(STRING), mode:derivedAt(DATE), jcr:lastModified(DATE), editorialTeam(LONG), jcr:successors(STRING), value2(STRING), title(STRING), value1(STRING), jcr:availableQueryOperators(STRING), mode:derivedFrom(STRING), jcr:primaryItemName(STRING), mode:isSessionScoped(BOOLEAN), jcr:frozenUuid(STRING), created(DATE), dimension(DECIMAL), jcr:isFullTextSearchable(BOOLEAN), version(STRING), jcr:defaultValues(STRING), mode:sharedUuid(STRING), basedOnVersion(STRING), validFrom(DATE), jcr:predecessors(STRING), width(DECIMAL), jcr:content(STRING), newsActive(BOOLEAN), content(STRING), mode:projectedNodeKey(STRING), jcr:language(STRING), jcr:valueConstraints(STRING), mode:sha1(STRING), jcr:activity(STRING), jcr:requiredPrimaryTypes(STRING), jcr:baseVersion(STRING), newsCategory(DECIMAL), jcr:requiredType(STRING), score(DECIMAL), jcr:autoCreated(BOOLEAN), shared(BOOLEAN), jcr:isAbstract(BOOLEAN), mode:externalNodeKey(STRING), jcr:hasOrderableChildNodes(BOOLEAN), additionalEditorialTeams(LONG), mode:lockToken(STRING), mode:kind(STRING), sharedForBankSectors(STRING), mode:alias(STRING), mode:columnTypeName(STRING), jcr:text(STRING), jcr:mimeType(STRING), mode:workspace(STRING), comment(STRING), owner(DECIMAL), modifiedBy(STRING), jcr:data(BINARY), jcr:port(STRING), validTo(DATE), jcr:protected(BOOLEAN), jcr:primaryType(STRING), mode:expirationDate(DATE), jcr:id(STRING), description(STRING), privileges(STRING), jcr:host(STRING), filename(STRING), jcr:lifecyclePolicy(STRING), archivedBy(STRING), contents(STRING), jcr:versionHistory(STRING), jcr:mixinTypes(STRING), workingCopy(STRING), length(DECIMAL), jcr:isQueryable(BOOLEAN), jcr:mandatory(BOOLEAN), jcr:lockIsDeep(BOOLEAN), image(STRING), height(DECIMAL), jcr:currentLifecycleState(STRING), jcr:nodeTypeName(STRING), jcr:isMixin(BOOLEAN), modifiedAt(DATE), username(STRING), name(STRING), userId(STRING), jcr:messageId(STRING), jcr:uuid(STRING), mimeType(STRING), jcr:multiple(BOOLEAN), jcr:title(STRING), importedByBankSectors(DECIMAL), jcr:onParentVersion(STRING), mode:nodeTypeName(STRING), jcr:supertypes(STRING), jcr:protocol(STRING), mode:propertyName(STRING), jcr:frozenPrimaryType(STRING), jcr:lockOwner(STRING), jcr:createdBy(STRING), jcr:isDeep(BOOLEAN), ownerBankSector(DECIMAL), latestLiveVersion(STRING), jcr:hold(STRING), jcr:configuration(STRING), jcr:description(STRING), jcr:statement(STRING), mode:isHeldBySession(BOOLEAN), mode:workspaces(STRING), jcr:copiedFrom(STRING), jcr:retentionPolicy(STRING), jcr:root(STRING), jcr:versionableUuid(STRING), liveVersion(STRING), mode:generated(BOOLEAN), sharedByBankSector(DECIMAL), mode:localName(STRING), mode:id(STRING), mode:depth(LONG), jcr:score(DOUBLE), jcr:path(STRING), jcr:name(STRING)]>",
              "                Index [content] <INDEX_SPECIFICATION=workflowState, provider=local, cost~=100, cardinality~=1, selectivity~=3.5324454E-5, constraints=[content.workflowState = CAST('3' AS LONG)]>",
              "                Index [content] <INDEX_SPECIFICATION=primaryTypes, provider=local, cost~=100, cardinality~=7597, selectivity~=0.111474685, constraints=[content.[jcr:primaryType] = 'intranet:article']>",
              "      Access [list]",
              "        Project [list] <PROJECT_COLUMNS=[list.[jcr:primaryType], list.[jcr:mixinTypes], list.[mode:localName], list.[mode:id], list.[mode:depth], list.[jcr:score], list.[jcr:path], list.[jcr:name]], PROJECT_COLUMN_TYPES=[STRING, STRING, STRING, STRING, LONG, DOUBLE, STRING, STRING]>",
              "          Select [list] <SELECT_CRITERIA=list.[jcr:primaryType] = 'intranet:list'>",
              "            Source [list] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=list, SOURCE_COLUMNS=[changeComment(STRING), reviewedAt(DATE), jcr:isCheckedOut(BOOLEAN), jcr:defaultPrimaryType(STRING), mode:uri(STRING), newsValidTo(DATE), mode:lockingSession(STRING), jcr:etag(STRING), archivedAt(DATE), jcr:created(DATE), reviewedBy(STRING), jcr:sameNameSiblings(BOOLEAN), jcr:encoding(STRING), jcr:workspace(STRING), workflowState(DECIMAL), jcr:activityTitle(STRING), jcr:mergeFailed(STRING), jcr:frozenMixinTypes(STRING), jcr:isQueryOrderable(BOOLEAN), type(DECIMAL), jcr:lastModifiedBy(STRING), bytes(BINARY), jcr:childVersionHistory(STRING), mode:synchronous(BOOLEAN), jcr:repository(STRING), mode:derivedAt(DATE), jcr:lastModified(DATE), editorialTeam(LONG), jcr:successors(STRING), value2(STRING), title(STRING), value1(STRING), jcr:availableQueryOperators(STRING), mode:derivedFrom(STRING), jcr:primaryItemName(STRING), mode:isSessionScoped(BOOLEAN), jcr:frozenUuid(STRING), created(DATE), dimension(DECIMAL), jcr:isFullTextSearchable(BOOLEAN), version(STRING), jcr:defaultValues(STRING), mode:sharedUuid(STRING), basedOnVersion(STRING), validFrom(DATE), jcr:predecessors(STRING), width(DECIMAL), jcr:content(STRING), newsActive(BOOLEAN), content(STRING), mode:projectedNodeKey(STRING), jcr:language(STRING), jcr:valueConstraints(STRING), mode:sha1(STRING), jcr:activity(STRING), jcr:requiredPrimaryTypes(STRING), jcr:baseVersion(STRING), newsCategory(DECIMAL), jcr:requiredType(STRING), score(DECIMAL), jcr:autoCreated(BOOLEAN), shared(BOOLEAN), jcr:isAbstract(BOOLEAN), mode:externalNodeKey(STRING), jcr:hasOrderableChildNodes(BOOLEAN), additionalEditorialTeams(LONG), mode:lockToken(STRING), mode:kind(STRING), sharedForBankSectors(STRING), mode:alias(STRING), mode:columnTypeName(STRING), jcr:text(STRING), jcr:mimeType(STRING), mode:workspace(STRING), comment(STRING), owner(DECIMAL), modifiedBy(STRING), jcr:data(BINARY), jcr:port(STRING), validTo(DATE), jcr:protected(BOOLEAN), jcr:primaryType(STRING), mode:expirationDate(DATE), jcr:id(STRING), description(STRING), privileges(STRING), jcr:host(STRING), filename(STRING), jcr:lifecyclePolicy(STRING), archivedBy(STRING), contents(STRING), jcr:versionHistory(STRING), jcr:mixinTypes(STRING), workingCopy(STRING), length(DECIMAL), jcr:isQueryable(BOOLEAN), jcr:mandatory(BOOLEAN), jcr:lockIsDeep(BOOLEAN), image(STRING), height(DECIMAL), jcr:currentLifecycleState(STRING), jcr:nodeTypeName(STRING), jcr:isMixin(BOOLEAN), modifiedAt(DATE), username(STRING), name(STRING), userId(STRING), jcr:messageId(STRING), jcr:uuid(STRING), mimeType(STRING), jcr:multiple(BOOLEAN), jcr:title(STRING), importedByBankSectors(DECIMAL), jcr:onParentVersion(STRING), mode:nodeTypeName(STRING), jcr:supertypes(STRING), jcr:protocol(STRING), mode:propertyName(STRING), jcr:frozenPrimaryType(STRING), jcr:lockOwner(STRING), jcr:createdBy(STRING), jcr:isDeep(BOOLEAN), ownerBankSector(DECIMAL), latestLiveVersion(STRING), jcr:hold(STRING), jcr:configuration(STRING), jcr:description(STRING), jcr:statement(STRING), mode:isHeldBySession(BOOLEAN), mode:workspaces(STRING), jcr:copiedFrom(STRING), jcr:retentionPolicy(STRING), jcr:root(STRING), jcr:versionableUuid(STRING), liveVersion(STRING), mode:generated(BOOLEAN), sharedByBankSector(DECIMAL), mode:localName(STRING), mode:id(STRING), mode:depth(LONG), jcr:score(DOUBLE), jcr:path(STRING), jcr:name(STRING)]>",
              "              Index [list] <INDEX_SPECIFICATION=primaryTypes, provider=local, cost~=100, cardinality~=15190, selectivity~=0.22289068, constraints=[list.[jcr:primaryType] = 'intranet:list']>",
              "    Access [tuple]",
              "      Project [tuple] <PROJECT_COLUMNS=[tuple.value1, tuple.value2, tuple.[jcr:primaryType], tuple.[jcr:mixinTypes], tuple.[mode:localName], tuple.[mode:id], tuple.[mode:depth], tuple.[jcr:score], tuple.[jcr:path], tuple.[jcr:name]], PROJECT_COLUMN_TYPES=[STRING, STRING, STRING, STRING, STRING, STRING, LONG, DOUBLE, STRING, STRING]>",
              "        Select [tuple] <SELECT_CRITERIA=tuple.value1 = CAST('17' AS LONG)>",
              "          Select [tuple] <SELECT_CRITERIA=tuple.[jcr:primaryType] = 'intranet:tuple'>",
              "            Source [tuple] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=tuple, SOURCE_COLUMNS=[changeComment(STRING), reviewedAt(DATE), jcr:isCheckedOut(BOOLEAN), jcr:defaultPrimaryType(STRING), mode:uri(STRING), newsValidTo(DATE), mode:lockingSession(STRING), jcr:etag(STRING), archivedAt(DATE), jcr:created(DATE), reviewedBy(STRING), jcr:sameNameSiblings(BOOLEAN), jcr:encoding(STRING), jcr:workspace(STRING), workflowState(DECIMAL), jcr:activityTitle(STRING), jcr:mergeFailed(STRING), jcr:frozenMixinTypes(STRING), jcr:isQueryOrderable(BOOLEAN), type(DECIMAL), jcr:lastModifiedBy(STRING), bytes(BINARY), jcr:childVersionHistory(STRING), mode:synchronous(BOOLEAN), jcr:repository(STRING), mode:derivedAt(DATE), jcr:lastModified(DATE), editorialTeam(LONG), jcr:successors(STRING), value2(STRING), title(STRING), value1(STRING), jcr:availableQueryOperators(STRING), mode:derivedFrom(STRING), jcr:primaryItemName(STRING), mode:isSessionScoped(BOOLEAN), jcr:frozenUuid(STRING), created(DATE), dimension(DECIMAL), jcr:isFullTextSearchable(BOOLEAN), version(STRING), jcr:defaultValues(STRING), mode:sharedUuid(STRING), basedOnVersion(STRING), validFrom(DATE), jcr:predecessors(STRING), width(DECIMAL), jcr:content(STRING), newsActive(BOOLEAN), content(STRING), mode:projectedNodeKey(STRING), jcr:language(STRING), jcr:valueConstraints(STRING), mode:sha1(STRING), jcr:activity(STRING), jcr:requiredPrimaryTypes(STRING), jcr:baseVersion(STRING), newsCategory(DECIMAL), jcr:requiredType(STRING), score(DECIMAL), jcr:autoCreated(BOOLEAN), shared(BOOLEAN), jcr:isAbstract(BOOLEAN), mode:externalNodeKey(STRING), jcr:hasOrderableChildNodes(BOOLEAN), additionalEditorialTeams(LONG), mode:lockToken(STRING), mode:kind(STRING), sharedForBankSectors(STRING), mode:alias(STRING), mode:columnTypeName(STRING), jcr:text(STRING), jcr:mimeType(STRING), mode:workspace(STRING), comment(STRING), owner(DECIMAL), modifiedBy(STRING), jcr:data(BINARY), jcr:port(STRING), validTo(DATE), jcr:protected(BOOLEAN), jcr:primaryType(STRING), mode:expirationDate(DATE), jcr:id(STRING), description(STRING), privileges(STRING), jcr:host(STRING), filename(STRING), jcr:lifecyclePolicy(STRING), archivedBy(STRING), contents(STRING), jcr:versionHistory(STRING), jcr:mixinTypes(STRING), workingCopy(STRING), length(DECIMAL), jcr:isQueryable(BOOLEAN), jcr:mandatory(BOOLEAN), jcr:lockIsDeep(BOOLEAN), image(STRING), height(DECIMAL), jcr:currentLifecycleState(STRING), jcr:nodeTypeName(STRING), jcr:isMixin(BOOLEAN), modifiedAt(DATE), username(STRING), name(STRING), userId(STRING), jcr:messageId(STRING), jcr:uuid(STRING), mimeType(STRING), jcr:multiple(BOOLEAN), jcr:title(STRING), importedByBankSectors(DECIMAL), jcr:onParentVersion(STRING), mode:nodeTypeName(STRING), jcr:supertypes(STRING), jcr:protocol(STRING), mode:propertyName(STRING), jcr:frozenPrimaryType(STRING), jcr:lockOwner(STRING), jcr:createdBy(STRING), jcr:isDeep(BOOLEAN), ownerBankSector(DECIMAL), latestLiveVersion(STRING), jcr:hold(STRING), jcr:configuration(STRING), jcr:description(STRING), jcr:statement(STRING), mode:isHeldBySession(BOOLEAN), mode:workspaces(STRING), jcr:copiedFrom(STRING), jcr:retentionPolicy(STRING), jcr:root(STRING), jcr:versionableUuid(STRING), liveVersion(STRING), mode:generated(BOOLEAN), sharedByBankSector(DECIMAL), mode:localName(STRING), mode:id(STRING), mode:depth(LONG), jcr:score(DOUBLE), jcr:path(STRING), jcr:name(STRING)]>",
              "              Index [tuple] <INDEX_SPECIFICATION=tupleValue1, provider=local, cost~=100, cardinality~=18, selectivity~=0.0022488756, constraints=[tuple.value1 = CAST('17' AS LONG)]>",
              "              Index [tuple] <INDEX_SPECIFICATION=primaryTypes, provider=local, cost~=100, cardinality~=8004, selectivity~=0.11744681, constraints=[tuple.[jcr:primaryType] = 'intranet:tuple']>"
          ]
      }
      


      Any ideas?

       

      Thanks,

       

      Regards,