Hi!
I’m currently running with CB 5.0.1 CE.
I created a primary index (create primary index on cmbucket
USING GSI) and removed all other indexes to be sure. Query (1) behaves as follows:
success | elapsed: 523.63ms | execution: 523.59ms | count: 12 | size: 562
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan2",
"covers": [
"cover ((meta(`cmbucket`).`id`))"
],
"index": "#primary",
"index_id": "16d89d8c69437dc0",
"keyspace": "cmbucket",
"namespace": "default",
"spans": [
{
"range": [
{
"high": "\"a;\"",
"inclusion": 1,
"low": "\"a:\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((cover ((meta(`cmbucket`).`id`)) like \"a:%\") and (cover ((meta(`cmbucket`).`id`)) like \"a:_role%\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((meta(`cmbucket`).`id`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT meta().id FROM `cmbucket` WHERE meta().id LIKE 'a:%' AND meta().id LIKE 'a:_role%'"
}
Query (2) also “works” using only primary index:
success | elapsed: 885.12ms | execution: 885.09ms | count: 2 | size: 114
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan2",
"index": "#primary",
"index_id": "16d89d8c69437dc0",
"keyspace": "cmbucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"a;\"",
"inclusion": 1,
"low": "\"a:\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"keyspace": "cmbucket",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((meta(`cmbucket`).`id`) like \"a:%\") and ((`cmbucket`.`contentId`) = \"c:YmRjMDU4YzgtZDg3Yy00\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(meta(`cmbucket`).`id`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT meta().id FROM `cmbucket` WHERE meta().id LIKE 'a:%' AND contentId='c:YmRjMDU4YzgtZDg3Yy00'"
}
All of this works, of course, but performance is far from usable in these cases.
The slower (2) is explained by having to fetch data from the data service, I assume.
With my old index (create index alias_index on cmbucket
(meta().id, contentId) where meta().id like ‘a:%’), the explains instead looked as follows.
Query (1):
success | elapsed: 528.31ms | execution: 528.21ms | count: 12 | size: 562
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan2",
"covers": [
"cover ((meta(`cmbucket`).`id`))",
"cover ((`cmbucket`.`contentId`))",
"cover ((meta(`cmbucket`).`id`))"
],
"filter_covers": {
"cover ((\"a:\" <= (meta(`cmbucket`).`id`)))": true,
"cover (((meta(`cmbucket`).`id`) < \"a;\"))": true,
"cover (((meta(`cmbucket`).`id`) like \"a:%\"))": true
},
"index": "alias_index",
"index_id": "308979758e93673e",
"index_projection": {
"primary_key": true
},
"keyspace": "cmbucket",
"namespace": "default",
"spans": [
{
"range": [
{
"high": "\"a;\"",
"inclusion": 1,
"low": "\"a:\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(cover (((meta(`cmbucket`).`id`) like \"a:%\")) and (cover ((meta(`cmbucket`).`id`)) like \"a:_role%\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((meta(`cmbucket`).`id`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT meta().id FROM `cmbucket` WHERE meta().id LIKE 'a:%' AND meta().id LIKE 'a:_role%'"
}
Query (2):
success | elapsed: 474.09ms | execution: 473.99ms | count: 2 | size: 114
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan2",
"covers": [
"cover ((meta(`cmbucket`).`id`))",
"cover ((`cmbucket`.`contentId`))",
"cover ((meta(`cmbucket`).`id`))"
],
"filter_covers": {
"cover ((\"a:\" <= (meta(`cmbucket`).`id`)))": true,
"cover (((meta(`cmbucket`).`id`) < \"a;\"))": true,
"cover (((meta(`cmbucket`).`id`) like \"a:%\"))": true
},
"index": "alias_index",
"index_id": "308979758e93673e",
"index_projection": {
"entry_keys": [
1
],
"primary_key": true
},
"keyspace": "cmbucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"a;\"",
"inclusion": 1,
"low": "\"a:\""
},
{
"high": "\"c:YmRjMDU4YzgtZDg3Yy00\"",
"inclusion": 3,
"low": "\"c:YmRjMDU4YzgtZDg3Yy00\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(cover (((meta(`cmbucket`).`id`) like \"a:%\")) and (cover ((`cmbucket`.`contentId`)) = \"c:YmRjMDU4YzgtZDg3Yy00\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((meta(`cmbucket`).`id`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT meta().id FROM `cmbucket` WHERE meta().id LIKE 'a:%' AND contentId='c:YmRjMDU4YzgtZDg3Yy00'"
}
An optimized index for query (2) (create index alias_index on
cmbucket(contentId) where meta().id like 'a:%'
) is explained by the query engine as follows:
success | elapsed: 3.59ms | execution: 3.56ms | count: 2 | size: 114
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan2",
"covers": [
"cover ((`cmbucket`.`contentId`))",
"cover ((meta(`cmbucket`).`id`))"
],
"filter_covers": {
"cover ((\"a:\" <= (meta(`cmbucket`).`id`)))": true,
"cover (((meta(`cmbucket`).`id`) < \"a;\"))": true,
"cover (((meta(`cmbucket`).`id`) like \"a:%\"))": true
},
"index": "alias_index",
"index_id": "572f95a8c063eeaa",
"keyspace": "cmbucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"c:YmRjMDU4YzgtZDg3Yy00\"",
"inclusion": 3,
"low": "\"c:YmRjMDU4YzgtZDg3Yy00\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(cover (((meta(`cmbucket`).`id`) like \"a:%\")) and (cover ((`cmbucket`.`contentId`)) = \"c:YmRjMDU4YzgtZDg3Yy00\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((meta(`cmbucket`).`id`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT meta().id FROM `cmbucket` WHERE meta().id LIKE 'a:%' AND contentId='c:YmRjMDU4YzgtZDg3Yy00'"
}
Regards,
Andreas Nilsson