So, I have setup a two node cluster using 4.5 CE and I have indexes duplicated on both nodes for fault tolerance purposes.
I have some indexes based on arrays and it looks like when I run queries that use these indexes, the query engine solicits both identical copies of those indexes (when I run explain).
What is the intended purpose of this behavior? I assumed it was for parallelization purposes but the “range” used for the indexes is the same.
Clarification would be awesome. Thank you.
All indexes are treated different during planning phase and Check Index Selection algorithm in https://dzone.com/articles/a-deep-dive-into-couchbase-n1ql-query-optimization
Once plan is picked index during runtime if there duplicate index index client will do load balancing.
Post the index definition of both indexes and Query EXPLAIN plan.
Thats what I figured, but in this case its not load balancing as its hitting both index nodes for the same query.
EXPLAIN SELECT id FROM test WHERE type = 1 AND value_1 <> 10 AND
(value_2 = 0 OR value_2 IS MISSING OR value_2 IS NULL) AND
ANY item_1 IN value_3 SATISFIES item_1.id = 5 AND ANY inner_item IN ARRAY_FLATTEN(OBJECT_VALUES(item_1.`values`), 1) SATISFIES inner_item IN ["random", 22] END END AND
ANY item_2 IN value_3 SATISFIES item_2.id = 6 AND ANY inner_item_2 IN ARRAY_FLATTEN(OBJECT_VALUES(item_2.`values`), 1) SATISFIES inner_item_2 IN ["random2", 44] END END
LIMIT 1
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"index": "test1-array-item_1-d4b05c",
"index_id": "bdab37791345a002",
"keyspace": "test",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"5"
],
"Inclusion": 3,
"Low": [
"5"
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"index": "test1-array-item_1-ce87b6",
"index_id": "4ff7e9e18cd7c19a",
"keyspace": "test",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"5"
],
"Inclusion": 3,
"Low": [
"5"
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"index": "test1-array-item_2-d4b05c",
"index_id": "eb3ba84a3bf127a1",
"keyspace": "test",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"6"
],
"Inclusion": 3,
"Low": [
"6"
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"index": "test1-array-item_2-ce87b6",
"index_id": "1e8589774e6c44b9",
"keyspace": "test",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"6"
],
"Inclusion": 3,
"Low": [
"6"
]
}
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan",
"index": "test1-value_1-value_4-d4b05c",
"index_id": "57adbb54fff242ea",
"keyspace": "test",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"10"
],
"Inclusion": 0,
"Low": [
"null"
]
}
},
{
"Range": {
"Inclusion": 0,
"Low": [
"10"
]
}
}
],
"using": "gsi"
}
}
]
}Sorry, forgot the query definition:
Node #1
CREATE INDEX `test1-array-item_2-ce87b6` ON `test`((distinct (array (`item_2`.`id`) for `item_2` in `value_3` when ((`item_2`.`id`) = 6) end))) WHERE (`type` = 1)
CREATE INDEX `test1-array-item_1-ce87b6` ON `test`((distinct (array (`item_1`.`id`) for `item_1` in `value_3` when ((`item_1`.`id`) = 5) end))) WHERE (`type` = 1)
Node #2
CREATE INDEX `test1-array-item_1-d4b05c` ON `test`((distinct (array (`item_1`.`id`) for `item_1` in `value_3` when ((`item_1`.`id`) = 5) end))) WHERE (`type` = 1)
CREATE INDEX `test1-array-item_2-d4b05c` ON `test`((distinct (array (`item_2`.`id`) for `item_2` in `value_3` when ((`item_2`.`id`) = 6) end))) WHERE (`type` = 1)In 4.5.0 regular indexes use only one and eliminate other as specified in index selection. Array indexes are not optimized. This has been taken care in later releases.
That makes sense! Thank you.