The index plan always starts out correctly using a distinct scan and only includes the array index that we want.
Then we hit the database with update and select n1ql queries to simulate load.
Then the index plan (for the same query) changes to an intersect scan and it brings in another index that takes minutes to execute.
We have simulated this issue on:
5.0.1 Community
5.1.1 Community
5.5.2 Enterprise
This does not happen on 4.6
I assume there has been a change in the way array indexes work, any advice on how to approach the problem is welcome.
--------------------DETAILS BELOW------------
QUERY:
SELECT DISTINCT META(per).id AS id, per.nameFirst, per.nameLast, per.client_ids, per.deceased
FROM deco_db_deco per
WHERE per.type = ‘person’ AND (per.nameLast) is not missing AND ANY array_element IN SUFFIXES(LOWER(per.nameLast)) SATISFIES array_element LIKE ‘smith%’ END
GOOD INDEX (this is the only index used immediately after stopping/starting the database):
CREATE INDEX autocomplete_nameLastD_03 ON deco_db_deco
(type,(distinct (array array_element for array_element in suffixes(lower(nameLast)) end)))
WHERE ((type = “person”) and (nameLast is not missing))
BAD INDEX (this is also included after some n1ql update/select load):
CREATE INDEX deco_type_index_03 ON deco_db_deco
(type)
INDEX PLANS BELOW:
INITIAL (GOOD) PLAN:
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “DistinctScan”,
“scan”: {
“#operator”: “IndexScan3”,
“as”: “per”,
“index”: “autocomplete_nameLastD_03”,
“index_id”: “8517cc45dea8af82”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “deco_db_deco”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““person””,
“inclusion”: 3,
“low”: ““person””
},
{
“high”: ““smiti””,
“inclusion”: 1,
“low”: ““smith””
}
]
}
],
“using”: “gsi”
}
},
{
“#operator”: “Fetch”,
“as”: “per”,
“keyspace”: “deco_db_deco”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((((per.type) = “person”) and ((per.nameLast) is not missing)) and any array_element in suffixes(lower((per.nameLast))) satisfies (array_element like “smith%”) end)”
},
{
“#operator”: “InitialProject”,
“distinct”: true,
“result_terms”: [
{
“as”: “id”,
“expr”: “(meta(per).id)”
},
{
“expr”: “(per.nameFirst)”
},
{
“expr”: “(per.nameLast)”
},
{
“expr”: “(per.client_ids)”
},
{
“expr”: “(per.deceased)”
}
]
},
{
“#operator”: “Distinct”
},
{
“#operator”: “FinalProject”
}
]
}
},
{
“#operator”: “Distinct”
}
]
},
“text”: “SELECT DISTINCT META(per).id AS id, per.nameFirst, per.nameLast, per.client_ids, per.deceased\nFROM deco_db_deco per \nWHERE per.type = ‘person’ AND (per.nameLast) is not missing AND ANY array_element IN SUFFIXES(LOWER(per.nameLast)) SATISFIES array_element LIKE ‘smith%’ END”
}
BAD PLAN (AFTER UPDATE/SELECT LOAD):
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IntersectScan”,
“scans”: [
{
“#operator”: “IndexScan3”,
“as”: “per”,
“index”: “deco_type_index_03”,
“index_id”: “3b9f6a819dbd212d”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “deco_db_deco”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““person””,
“inclusion”: 3,
“low”: ““person””
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “DistinctScan”,
“scan”: {
“#operator”: “IndexScan3”,
“as”: “per”,
“index”: “autocomplete_nameLastY_03”,
“index_id”: “748628961184c17f”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “deco_db_deco”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““person””,
“inclusion”: 3,
“low”: ““person””
}
]
}
],
“using”: “gsi”
}
}
]
},
{
“#operator”: “Fetch”,
“as”: “per”,
“keyspace”: “deco_db_deco”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((((per.type) = “person”) and ((per.nameLast) is not missing)) and any array_element in suffixes(lower((per.nameLast))) satisfies (array_element like “smith%”) end)”
},
{
“#operator”: “InitialProject”,
“distinct”: true,
“result_terms”: [
{
“as”: “id”,
“expr”: “(meta(per).id)”
},
{
“expr”: “(per.nameFirst)”
},
{
“expr”: “(per.nameLast)”
},
{
“expr”: “(per.client_ids)”
},
{
“expr”: “(per.deceased)”
}
]
},
{
“#operator”: “Distinct”
},
{
“#operator”: “FinalProject”
}
]
}
},
{
“#operator”: “Distinct”
}
]
},
“text”: “SELECT DISTINCT META(per).id AS id, per.nameFirst, per.nameLast, per.client_ids, per.deceased\nFROM deco_db_deco per \nWHERE per.type = ‘person’ AND (per.nameLast) is not missing AND ANY array_element IN SUFFIXES(LOWER(per.nameLast)) SATISFIES array_element LIKE ‘smith%’ END”
}