Speed up slow sql2 query with indexes

[prinzs]

Hi,

we use the following query to check, if any child (in any depth) of a certain node has a certain permission with the privilege "my:readPath" and the name of a user:

SELECT privileges, name FROM [mytype:Permission] AS permission WHERE permission.[privileges] = 'my:readPath' AND permission.[name] = 'USERID' AND ISDESCENDANTNODE('/path/of/node/to/check')";

This query is very slow if there are around 10000 nodes or more. We tried to replace "ISDESCENDANTNODE" without success:

SELECT privileges, name FROM [mytype:Permission] AS permission WHERE permission.[privileges] = 'my:readPath' AND permission.[name] = 'USERID' AND PATH([mytype:Permission]) LIKE ('/path/of/node/to/check');

We also tried to use a XPATH query which also was very slow.

We configured the following indexes in our repo:

"indexes" : {

      "permissionsByPrivileges" : {

         "kind" : "value",

         "provider" : "local",

         "synchronous" : "true",

         "nodeType" : "mytype:Permission",

         "columns" : "privileges(STRING)"

      },

      "permissionsByName" : {

         "kind" : "value",

         "provider" : "local",

         "synchronous" : "true",

         "nodeType" : "mytype:Permission",

         "columns" : "name(STRING)"

      },

      "nodesByPath" : {

         "kind" : "value",

         "provider" : "local",

         "synchronous" : "true",

         "nodeType" : "nt:base",

         "columns" : "jcr:path(PATH)"

      }

   }

Shouldn't these indexes be used automatically for the described query or do we miss something? Could we rewrite the query or add indexes to speed up the query?

Thanks for your help!

Simon P.

[hchiorean]

ISDESCENDANTNODE, ISCHILDNODE and other "join" constraints are not used by indexes. Indexes are mostly used in "value constraints" with a few exceptions like FTS, CHILDCOUNT and a few others (see the ModeShape docs)

Your index definitions look fine and I would expect that at least permissionsByPrivileges and permissionsByName are used in both cases. Also nodesByPath should be used in the second case (although indexing nt:base by path will most likely result in a huge index and some overhead since you're indexing the path of all the nodes in the repo). To check if an index definition is used or not in your query, you should look at the query plan: Query and search - ModeShape 5 - Project Documentation Editor

As always, make sure you use the latest version (5.1.0) since we're always fixing bugs.

[prinzs]

Thanks for the fast reply!

The query plan is telling me the following:

- The ISDESCENDANTNODE query uses ONE index ("DescendantsByPath") but none of our own configured indexes

- The second query (by path) uses NO indexes

Any ideas why no index is beeing used with the second query?

Is it possible to get all configured indexes somehow? I would like to check if they are configured correctly.

EDIT:

Got all indexes with the IndexManager class:

{permissionsByPrivileges=permissionsByPrivileges@local nodeType=my:Permission column=privileges(STRING) kind=VALUE sync=true workspaces=*, permissionsByName=permissionsByName@local nodeType=my:Permission column=name(STRING) kind=VALUE sync=true workspaces=*, nodesByPath=nodesByPath@local nodeType=nt:base column=jcr:path(PATH) kind=VALUE sync=true workspaces=*}

It looks like the are configured correctly...

[hchiorean]

If you remove the PATH or ISDESCENDANTNODE criteria from the SELECT clause, are your indexes still not being used ? Also, can you post the query plan string in both cases (queries) please ?

ModeShape cannot use multiple indexes at the same time, so it selects one index based on its selectivity. One thing that may be happening is that an internal index (like DescendantsByPath or NodeByPath) is chosen over your external indexes.

Also, perhaps more importantly, did you profile to see where time is actually spent ? Are you sure the problem lies in the index selection and not somewhere else ?

[prinzs]

Here are the query plans:

SELECT privileges, name FROM [mytype:Permission] AS permission WHERE permission.[privileges] = 'my:readPath' AND permission.[name] = 'USERID' AND ISDESCENDANTNODE('/path/of/node/to/check')":

Access [permission]

  Project [permission] <PROJECT_COLUMNS=[permission.name, permission.privileges, permission.[jcr:primaryType], permission.[jcr:mixinTypes], permission.[mode:localName], permission.[mode:id], permission.[mode:depth], permission.[jcr:score], permission.[jcr:path], permission.[jcr:name]], PROJECT_COLUMN_TYPES=[STRING, STRING, STRING, STRING, STRING, STRING, LONG, DOUBLE, STRING, STRING]>

    Select [permission] <SELECT_CRITERIA=permission.privileges = 'my:readPath'>

      Select [permission] <SELECT_CRITERIA=permission.name IN ($uid0,$uid1,$uid2,$uid3)>

        Select [permission] <SELECT_CRITERIA=ISDESCENDANTNODE(permission,'/workspace')>

          Select [permission] <SELECT_CRITERIA=permission.[jcr:primaryType] = 'my:Permission'>

            Source [permission] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=permission, SOURCE_COLUMNS=[jcr:isCheckedOut(BOOLEAN), jcr:defaultPrimaryType(STRING), mode:uri(STRING), my:height(LONG), mode:lockingSession(STRING), jcr:etag(STRING), jcr:created(DATE), my:userModificationDate(DATE), jcr:sameNameSiblings(BOOLEAN), jcr:encoding(STRING), jcr:workspace(STRING), jcr:activityTitle(STRING), jcr:mergeFailed(STRING), jcr:frozenMixinTypes(STRING), jcr:isQueryOrderable(BOOLEAN), my:maxSpace(DECIMAL), jcr:lastModifiedBy(STRING), my:failedAttempts(DECIMAL), my:scanDate(DATE), jcr:childVersionHistory(STRING), mode:synchronous(BOOLEAN), jcr:repository(STRING), mode:derivedAt(DATE), jcr:lastModified(DATE), jcr:successors(STRING), jcr:availableQueryOperators(STRING), mode:derivedFrom(STRING), jcr:primaryItemName(STRING), mode:isSessionScoped(BOOLEAN), jcr:frozenUuid(STRING), jcr:isFullTextSearchable(BOOLEAN), jcr:defaultValues(STRING), mode:sharedUuid(STRING), jcr:predecessors(STRING), jcr:content(STRING), mode:projectedNodeKey(STRING), jcr:language(STRING), my:watchers(STRING), jcr:valueConstraints(STRING), mode:sha1(STRING), jcr:activity(STRING), jcr:requiredPrimaryTypes(STRING), jcr:baseVersion(STRING), jcr:requiredType(STRING), jcr:autoCreated(BOOLEAN), jcr:isAbstract(BOOLEAN), mode:externalNodeKey(STRING), jcr:hasOrderableChildNodes(BOOLEAN), my:password(STRING), mode:lockToken(STRING), mode:kind(STRING), mode:alias(STRING), mode:columnTypeName(STRING), jcr:text(STRING), jcr:mimeType(STRING), mode:workspace(STRING), my:purgeDate(DATE), jcr:data(BINARY), jcr:port(STRING), jcr:protected(BOOLEAN), jcr:primaryType(STRING), my:linkedFolderId(STRING), mode:expirationDate(DATE), jcr:id(STRING), my:validUntil(DATE), jcr:host(STRING), jcr:lifecyclePolicy(STRING), jcr:versionHistory(STRING), jcr:mixinTypes(STRING), jcr:isQueryable(BOOLEAN), jcr:mandatory(BOOLEAN), jcr:lockIsDeep(BOOLEAN), my:width(LONG), jcr:currentLifecycleState(STRING), jcr:nodeTypeName(STRING), jcr:isMixin(BOOLEAN), name(STRING), jcr:messageId(STRING), jcr:uuid(STRING), jcr:multiple(BOOLEAN), jcr:title(STRING), jcr:onParentVersion(STRING), mode:nodeTypeName(STRING), jcr:supertypes(STRING), privileges(STRING), jcr:protocol(STRING), mode:propertyName(STRING), jcr:frozenPrimaryType(STRING), jcr:lockOwner(STRING), jcr:createdBy(STRING), jcr:isDeep(BOOLEAN), jcr:hold(STRING), jcr:configuration(STRING), my:checksum(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), mode:generated(BOOLEAN), mode:localName(STRING), mode:id(STRING), mode:depth(LONG), jcr:score(DOUBLE), jcr:path(STRING), jcr:name(STRING)]>

              Index [permission] <INDEX_SPECIFICATION=DescendantsByPath, provider=<implicit>, cost~=1000, cardinality~=10000, selectivity~=?, constraints=[ISDESCENDANTNODE(permission,'/workspace')], path=/workspace, INDEX_USED=true>

SELECT privileges, name FROM [mytype:Permission] AS permission WHERE permission.[privileges] = 'my:readPath' AND permission.[name] = 'USERID' AND PATH([mytype:Permission]) LIKE ('/path/of/node/to/check'):

Access [permission]

  Project [permission] <PROJECT_COLUMNS=[permission.name, permission.privileges, permission.[jcr:primaryType], permission.[jcr:mixinTypes], permission.[mode:localName], permission.[mode:id], permission.[mode:depth], permission.[jcr:score], permission.[jcr:path], permission.[jcr:name]], PROJECT_COLUMN_TYPES=[STRING, STRING, STRING, STRING, STRING, STRING, LONG, DOUBLE, STRING, STRING]>

    Select [permission] <SELECT_CRITERIA=permission.privileges = 'my:readPath'>

      Select [permission] <SELECT_CRITERIA=permission.name IN ($uid0,$uid1,$uid2,$uid3)>

        Select [permission] <SELECT_CRITERIA=PATH(permission) LIKE '/workspace'>

          Select [permission] <SELECT_CRITERIA=permission.[jcr:primaryType] = 'my:Permission'>

            Source [permission] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=permission, SOURCE_COLUMNS=[jcr:isCheckedOut(BOOLEAN), jcr:defaultPrimaryType(STRING), mode:uri(STRING), my:height(LONG), mode:lockingSession(STRING), jcr:etag(STRING), jcr:created(DATE), my:userModificationDate(DATE), jcr:sameNameSiblings(BOOLEAN), jcr:encoding(STRING), jcr:workspace(STRING), jcr:activityTitle(STRING), jcr:mergeFailed(STRING), jcr:frozenMixinTypes(STRING), jcr:isQueryOrderable(BOOLEAN), my:maxSpace(DECIMAL), jcr:lastModifiedBy(STRING), my:failedAttempts(DECIMAL), my:scanDate(DATE), jcr:childVersionHistory(STRING), mode:synchronous(BOOLEAN), jcr:repository(STRING), mode:derivedAt(DATE), jcr:lastModified(DATE), jcr:successors(STRING), jcr:availableQueryOperators(STRING), mode:derivedFrom(STRING), jcr:primaryItemName(STRING), mode:isSessionScoped(BOOLEAN), jcr:frozenUuid(STRING), jcr:isFullTextSearchable(BOOLEAN), jcr:defaultValues(STRING), mode:sharedUuid(STRING), jcr:predecessors(STRING), jcr:content(STRING), mode:projectedNodeKey(STRING), jcr:language(STRING), my:watchers(STRING), jcr:valueConstraints(STRING), mode:sha1(STRING), jcr:activity(STRING), jcr:requiredPrimaryTypes(STRING), jcr:baseVersion(STRING), jcr:requiredType(STRING), jcr:autoCreated(BOOLEAN), jcr:isAbstract(BOOLEAN), mode:externalNodeKey(STRING), jcr:hasOrderableChildNodes(BOOLEAN), my:password(STRING), mode:lockToken(STRING), mode:kind(STRING), mode:alias(STRING), mode:columnTypeName(STRING), jcr:text(STRING), jcr:mimeType(STRING), mode:workspace(STRING), my:purgeDate(DATE), jcr:data(BINARY), jcr:port(STRING), jcr:protected(BOOLEAN), jcr:primaryType(STRING), my:linkedFolderId(STRING), mode:expirationDate(DATE), jcr:id(STRING), my:validUntil(DATE), jcr:host(STRING), jcr:lifecyclePolicy(STRING), jcr:versionHistory(STRING), jcr:mixinTypes(STRING), jcr:isQueryable(BOOLEAN), jcr:mandatory(BOOLEAN), jcr:lockIsDeep(BOOLEAN), my:width(LONG), jcr:currentLifecycleState(STRING), jcr:nodeTypeName(STRING), jcr:isMixin(BOOLEAN), name(STRING), jcr:messageId(STRING), jcr:uuid(STRING), jcr:multiple(BOOLEAN), jcr:title(STRING), jcr:onParentVersion(STRING), mode:nodeTypeName(STRING), jcr:supertypes(STRING), privileges(STRING), jcr:protocol(STRING), mode:propertyName(STRING), jcr:frozenPrimaryType(STRING), jcr:lockOwner(STRING), jcr:createdBy(STRING), jcr:isDeep(BOOLEAN), jcr:hold(STRING), jcr:configuration(STRING), my:checksum(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), mode:generated(BOOLEAN), mode:localName(STRING), mode:id(STRING), mode:depth(LONG), jcr:score(DOUBLE), jcr:path(STRING), jcr:name(STRING)]>

SELECT privileges, name FROM [mytype:Permission] AS permission WHERE permission.[privileges] = 'my:readPath' AND permission.[name] = 'USERID:

Access [permission]

  Project [permission] <PROJECT_COLUMNS=[permission.name, permission.privileges, permission.[jcr:primaryType], permission.[jcr:mixinTypes], permission.[mode:localName], permission.[mode:id], permission.[mode:depth], permission.[jcr:score], permission.[jcr:path], permission.[jcr:name]], PROJECT_COLUMN_TYPES=[STRING, STRING, STRING, STRING, STRING, STRING, LONG, DOUBLE, STRING, STRING]>

    Select [permission] <SELECT_CRITERIA=permission.privileges = 'my:readPath'>

      Select [permission] <SELECT_CRITERIA=permission.name IN ($uid0,$uid1,$uid2,$uid3)>

        Select [permission] <SELECT_CRITERIA=permission.[jcr:primaryType] = 'my:Permission'>

          Source [permission] <SOURCE_NAME=__ALLNODES__, SOURCE_ALIAS=permission, SOURCE_COLUMNS=[jcr:isCheckedOut(BOOLEAN), jcr:defaultPrimaryType(STRING), mode:uri(STRING), my:height(LONG), mode:lockingSession(STRING), jcr:etag(STRING), jcr:created(DATE), my:userModificationDate(DATE), jcr:sameNameSiblings(BOOLEAN), jcr:encoding(STRING), jcr:workspace(STRING), jcr:activityTitle(STRING), jcr:mergeFailed(STRING), jcr:frozenMixinTypes(STRING), jcr:isQueryOrderable(BOOLEAN), my:maxSpace(DECIMAL), jcr:lastModifiedBy(STRING), my:failedAttempts(DECIMAL), my:scanDate(DATE), jcr:childVersionHistory(STRING), mode:synchronous(BOOLEAN), jcr:repository(STRING), mode:derivedAt(DATE), jcr:lastModified(DATE), jcr:successors(STRING), jcr:availableQueryOperators(STRING), mode:derivedFrom(STRING), jcr:primaryItemName(STRING), mode:isSessionScoped(BOOLEAN), jcr:frozenUuid(STRING), jcr:isFullTextSearchable(BOOLEAN), jcr:defaultValues(STRING), mode:sharedUuid(STRING), jcr:predecessors(STRING), jcr:content(STRING), mode:projectedNodeKey(STRING), jcr:language(STRING), my:watchers(STRING), jcr:valueConstraints(STRING), mode:sha1(STRING), jcr:activity(STRING), jcr:requiredPrimaryTypes(STRING), jcr:baseVersion(STRING), jcr:requiredType(STRING), jcr:autoCreated(BOOLEAN), jcr:isAbstract(BOOLEAN), mode:externalNodeKey(STRING), jcr:hasOrderableChildNodes(BOOLEAN), my:password(STRING), mode:lockToken(STRING), mode:kind(STRING), mode:alias(STRING), mode:columnTypeName(STRING), jcr:text(STRING), jcr:mimeType(STRING), mode:workspace(STRING), my:purgeDate(DATE), jcr:data(BINARY), jcr:port(STRING), jcr:protected(BOOLEAN), jcr:primaryType(STRING), my:linkedFolderId(STRING), mode:expirationDate(DATE), jcr:id(STRING), my:validUntil(DATE), jcr:host(STRING), jcr:lifecyclePolicy(STRING), jcr:versionHistory(STRING), jcr:mixinTypes(STRING), jcr:isQueryable(BOOLEAN), jcr:mandatory(BOOLEAN), jcr:lockIsDeep(BOOLEAN), my:width(LONG), jcr:currentLifecycleState(STRING), jcr:nodeTypeName(STRING), jcr:isMixin(BOOLEAN), name(STRING), jcr:messageId(STRING), jcr:uuid(STRING), jcr:multiple(BOOLEAN), jcr:title(STRING), jcr:onParentVersion(STRING), mode:nodeTypeName(STRING), jcr:supertypes(STRING), privileges(STRING), jcr:protocol(STRING), mode:propertyName(STRING), jcr:frozenPrimaryType(STRING), jcr:lockOwner(STRING), jcr:createdBy(STRING), jcr:isDeep(BOOLEAN), jcr:hold(STRING), jcr:configuration(STRING), my:checksum(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), mode:generated(BOOLEAN), mode:localName(STRING), mode:id(STRING), mode:depth(LONG), jcr:score(DOUBLE), jcr:path(STRING), jcr:name(STRING)]>

So the indexes aren't even used in the last case without ISDESCENDANTNODE or PATH...

Whats taking long is definitely the query, although I'm not sure why it is taking so long. I suspected it is because of the indexes not beeing used, which is very likely as seen above. I think it is very odd that even with the simple query without ISDESCENDANTNODE or PATH the indexes are not beeing used.

[hchiorean]

If you can provide a standalone test case for this particular issue (you can look for example at modeshape/LocalIndexProviderTest.java at master · ModeShape/modeshape · GitHub) feel free to log a JIRA and I'll look at it locally some more.

Otherwise, assuming the indexes in the above case are enabled and data is stored on disk for them, they should be used by the query engine.