Why is the query intersecting indexes when one index has all it needs?

I would expect the query below to use only the IDX_AUDIT6 index, but when i explain it does an intersect with the doc_type index. Can somebody explain to me why?

select acxiom.errors.error_code, id, timestamp
from order_history
where doc_type = “other” and acxiom.outgoing_request.activityTypeCode
and acxiom.errors.developer_message like "
%Timeout_%" and timestamp > “+ timeParam +”;

CREATE INDEX IDX_AUDIT6 ON order_history((doc_type,(acxiom.errors.error_code),(acxiom.errors.developer_message),_id,timestamp))
WHERE ((doc_type = “other”) and ((acxiom.errors.developer_message) is not null) and ((acxiom.outgoing_request.activityTypeCode) is not null)) WITH { “num_replica”:1 }

the other index it is also using:

CREATE INDEX doc_type ON order_history(doc_type) WITH { “defer_build”:true, “num_replica”:1 }

What version of CB? Post the EXPLAIN. doc_type index is subset of others it should have ignored.

The following is right index for query (check id or _id)

CREATE INDEX IDX_AUDIT6 ON order_history(acxiom.outgoing_request.activityTypeCode,
                                        timestamp,
                                        acxiom.errors.developer_message,
                                        acxiom.errors.error_code, id)
WHERE doc_type = "other";

https://blog.couchbase.com/create-right-index-get-right-performance/

here is the explain output:

{
“plan”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “IntersectScan”,
“scans”: [
{
#operator”: “IndexScan2”,
“index”: “IDX_AUDIT6”,
“index_id”: “37d35bba842ff805”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “order_history”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““other””,
“inclusion”: 3,
“low”: ““other””
}
]
}
],
“using”: “gsi”
},
{
#operator”: “IndexScan2”,
“index”: “doc_type”,
“index_id”: “eddd8dfef551f305”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “order_history”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““other””,
“inclusion”: 3,
“low”: ““other””
}
]
}
],
“using”: “gsi”
}
]
},
{
#operator”: “Fetch”,
“keyspace”: “order_history”,
“namespace”: “default”
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “Filter”,
“condition”: “(((((order_history.doc_type) = “other”) and (((order_history.acxiom).outgoing_request).activityTypeCode)) and ((((order_history.acxiom).errors).developer_message) like “%Timeout%”)) and (”+ timeParam +" < (order_history.timestamp)))"
},
{
#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(((order_history.acxiom).errors).error_code)”
},
{
“expr”: “(order_history._id)”
},
{
“expr”: “(order_history.timestamp)”
}
]
},
{
#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “select acxiom.errors.error_code, id, timestamp\r\n from order_history\r\n where doc_type = “other” and acxiom.outgoing_request.activityTypeCode\r\n and acxiom.errors.developer_message like "%Timeout_%” and timestamp > “+ timeParam +”;"
}

It looks like you are using old version of CB. Fixed by MB-26483
Option 1) Specify index hint avoid IntersectScan. Example : FROM order_history USE INDEX (IDX_AUDIT6)
Option 2) Upgrade CB 6.0 and try it.

i can’t upgrade at this point(bunch of red tape has to happen first), but i will try the use index parameter. thank you for the help