What could we do to speed up the following query?
The problem is that it sporadically fails with a Timeout after 120 seconds. Sometimes it is really fast (1sec)
We are on CB 6.0 CE with a 6 node cluster. Indexes are distributed manually in the cluster, so that every node has some of the indexes.
select meta(`mybucket`).id from `mybucket` WHERE datastore_id = 'db1' AND ((parent_id = '' OR parent_id IS NULL) AND OBJECT_LENGTH(IFMISSINGORNULL(variantids,{})) > 0 ) AND ((parent_id = '' OR parent_id IS NULL) ) AND folder IN ['default','folder2','folder3'] AND processingstatus = 0 AND ((master_id = '' OR master_id IS NULL)) AND created_at BETWEEN '1970-01-01 00:00:00.000' AND '2019-05-29 10:34:00.000' ORDER BY created_at ASC LIMIT 2147483647
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "OrderedIntersectScan",
"scans": [
{
"#operator": "IndexScan3",
"index": "idx_created_at",
"index_id": "b3b13ecd6d31bead",
"index_order": [
{
"keypos": 0
},
{
"keypos": 1
}
],
"index_projection": {
"primary_key": true
},
"keyspace": "mybucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "\"2019-05-29 10:34:00.000\"",
"inclusion": 3,
"low": "\"1970-01-01 00:00:00.000\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"index": "idx_datastore",
"index_id": "8be9bc8e4017ecf6",
"index_projection": {
"primary_key": true
},
"keyspace": "mybucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "\"default\"",
"inclusion": 3,
"low": "\"default\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "\"folder2\"",
"inclusion": 3,
"low": "\"folder2\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "\"folder3\"",
"inclusion": 3,
"low": "\"folder3\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"index": "idx_processingstatus",
"index_id": "f8bea1cb86a2023f",
"index_projection": {
"primary_key": true
},
"keyspace": "mybucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "0",
"inclusion": 3,
"low": "0"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"index": "idx_variantids_length2",
"index_id": "67ffd3cb9a06dbfe",
"index_projection": {
"primary_key": true
},
"keyspace": "mybucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "\"\"",
"inclusion": 3,
"low": "\"\""
},
{
"inclusion": 0,
"low": "0"
}
]
},
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "null",
"inclusion": 3,
"low": "null"
},
{
"inclusion": 0,
"low": "0"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"index": "idx_childrenids_length2",
"index_id": "d8c34a2d5084f2ec",
"index_projection": {
"primary_key": true
},
"keyspace": "mybucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "\"\"",
"inclusion": 3,
"low": "\"\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"db1\"",
"inclusion": 3,
"low": "\"db1\""
},
{
"high": "null",
"inclusion": 3,
"low": "null"
}
]
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"keyspace": "mybucket",
"namespace": "default"
},
{
"#operator": "Parallel",
"maxParallelism": 1,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((((((`mybucket`.`datastore_id`) = \"db1\") and ((((`mybucket`.`parent_id`) = \"\") or ((`mybucket`.`parent_id`) is null)) and (0 < object_length(ifmissingornull((`mybucket`.`variantids`), {}))))) and (((`mybucket`.`parent_id`) = \"\") or ((`mybucket`.`parent_id`) is null))) and ((`mybucket`.`folder`) in [\"default\", \"folder2\", \"folder3\"])) and ((`mybucket`.`processingstatus`) = 0)) and (((`mybucket`.`master_id`) = \"\") or ((`mybucket`.`master_id`) is null))) and ((`mybucket`.`created_at`) between \"1970-01-01 00:00:00.000\" and \"2019-05-29 10:34:00.000\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(meta(`mybucket`).`id`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "2147483647"
}
]
},
"text": "select meta(`mybucket`).id from `mybucket` WHERE datastore_id = 'db1' AND ((parent_id = '' OR parent_id IS NULL) AND OBJECT_LENGTH(IFMISSINGORNULL(variantids,{})) > 0 ) AND ((parent_id = '' OR parent_id IS NULL) ) AND folder IN ['default','folder2','folder3'] AND processingstatus = 0 AND ((master_id = '' OR master_id IS NULL)) AND created_at BETWEEN '1970-01-01 00:00:00.000' AND '2019-05-29 10:34:00.000' ORDER BY created_at ASC LIMIT 2147483647"
}