[
https://issues.apache.org/jira/browse/OAK-4887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15595899#comment-15595899
]
Alexander Klimetschek commented on OAK-4887:
--------------------------------------------
h4. Index
Note that {{jcr:created}} is not covered by the index.
{noformat}
{
"jcr:primaryType": "oak:QueryIndexDefinition",
"compatVersion": 2,
"type": "lucene",
"async": "async",
"evaluatePathRestrictions": true,
"reindex": false,
"reindexCount": 1,
"aggregates": {
"jcr:primaryType": "nt:unstructured",
"dam:Asset": {
"jcr:primaryType": "nt:unstructured",
"include0": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content"
},
"include1": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content/metadata"
},
"include2": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content/metadata/*"
},
"include3": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content/renditions"
},
"include4": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content/renditions/original"
},
"include5": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content/renditions/original/jcr:content"
},
"include6": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content/comments"
},
"include7": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content/comments/*"
},
"include8": {
"jcr:primaryType": "nt:unstructured",
"path": "jcr:content/usages"
}
}
},
"indexRules": {
"jcr:primaryType": "nt:unstructured",
"dam:Asset": {
"jcr:primaryType": "nt:unstructured",
"properties": {
"jcr:primaryType": "nt:unstructured",
"cqTags": {
"jcr:primaryType": "nt:unstructured",
"nodeScopeIndex": true,
"useInSuggest": true,
"propertyIndex": true,
"useInSpellcheck": true,
"name": "jcr:content/metadata/cq:tags"
},
"dcFormat": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"analyzed": true,
"name": "jcr:content/metadata/dc:format"
},
"damStatus": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/metadata/dam:status"
},
"videoCodec": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/metadata/videoCodec"
},
"audioCodec": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/metadata/audioCodec"
},
"dcTitle": {
"jcr:primaryType": "nt:unstructured",
"nodeScopeIndex": true,
"useInSuggest": true,
"propertyIndex": true,
"useInSpellcheck": true,
"name": "jcr:content/metadata/dc:title",
"boost": 2
},
"dcDescription": {
"jcr:primaryType": "nt:unstructured",
"nodeScopeIndex": true,
"useInSuggest": true,
"propertyIndex": true,
"useInSpellcheck": true,
"name": "jcr:content/metadata/dc:description"
},
"xmpMMInstanceId": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/metadata/xmpMM:InstanceID"
},
"xmpMMDocumentId": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/metadata/xmpMM:DocumentID"
},
"damSha1": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/metadata/dam:sha1"
},
"hasValidMetadata": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/hasValidMetadata",
"type": "Boolean"
},
"videoBitrate": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/metadata/videoBitrate"
},
"audioBitRate": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/metadata/audioBitrate"
},
"usedBy": {
"jcr:primaryType": "nt:unstructured",
"propertyIndex": true,
"name": "jcr:content/usages/usedBy"
},
"jcrLastModified": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/jcr:lastModified",
"type": "Date"
},
"expirationDate": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/metadata/prism:expirationDate",
"type": "Date"
},
"onTime": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/onTime",
"type": "Date"
},
"offTime": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/offTime",
"type": "Date"
},
"damSize": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/metadata/dam:size",
"type": "Long"
},
"averageRating": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/averageRating",
"type": "Double"
},
"tiffImageWidth": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/metadata/tiff:ImageWidth",
"type": "Long"
},
"tiffImageLength": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/metadata/tiff:ImageLength",
"type": "Long"
},
"score": {
"jcr:primaryType": "nt:unstructured",
"ordered": true,
"propertyIndex": true,
"name": "jcr:content/usages/dam:score",
"type": "Long"
}
}
}
}
}
{noformat}
h4. Query without order by
{noformat}
//element(*, dam:Asset)
-> cost 464
cost using filter Filter(query=select [jcr:path], [jcr:score], * from
[dam:Asset] as a /* xpath: //element(*, dam:Asset) */, path=*)
cost for reference is Infinity
cost for property is Infinity
cost for nodeType is Infinity
cost for lucene-property[/oak:index/damAssetLucene] is 464.0
cost for aggregate lucene is Infinity
cost for solr is Infinity
cost for traverse is 226100.0
{noformat}
h4. Query with order by of unindexed property
{noformat}
//element(*, dam:Asset) order by @jcr:created
-> cost 464
cost using filter Filter(query=select [jcr:path], [jcr:score], * from
[dam:Asset] as a order by [jcr:created] /* xpath: //element(*, dam:Asset) order
by @jcr:created */, path=*)
cost for reference is Infinity
cost for property is Infinity
cost for nodeType is Infinity
cost for lucene-property[/oak:index/damAssetLucene] is 464.0
cost for aggregate lucene is Infinity
cost for solr is Infinity
cost for traverse is 226100.0
{noformat}
With a large amount of dam:Asset nodes in the repository, and thus a large
index, this leads to a complete index traversal (to be able to sort) and a slow
query (even if a limit is set on the query results).
> Query cost estimation: ordering by an unindexed property not reflected
> ----------------------------------------------------------------------
>
> Key: OAK-4887
> URL: https://issues.apache.org/jira/browse/OAK-4887
> Project: Jackrabbit Oak
> Issue Type: Improvement
> Components: query
> Affects Versions: 1.4.2
> Reporter: Alexander Klimetschek
> Assignee: Thomas Mueller
> Fix For: 1.6
>
>
> A query that orders by an unindexed property seems to have no effect on the
> cost estimation, compared to the same query without the order by, although it
> has a big impact on the execution performance for larger results/indexes.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
