Slow on compound index

SQL++
1

Trying out the N1QL as we are considering to migrate to it.

Just at initial stage, testing performance on very simple queries, the response needs 3 secs.

I’ve tried that if I remove the index compound to 2 fields, the response is normal, but when I try adding the fields to 3, the problem comes out.

the index is:
CREATE INDEX idx_user_test ON iptb(t,(d.del),(d.uName)) WHERE (t = “user”) WITH({“index_type” : “forestdb”})

my query is like this:
select * from iptb use index(idx_user_test) where t = “user” AND d.del IS MISSING and d.uName = "user1@email.com"

with explain:
[
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “idx_user_test”,
“keyspace”: “iptb”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“successor(“user”)”
],
“Inclusion”: 1,
“Low”: [
"“user”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “iptb”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((((iptb.t) = “user”) and (((iptb.d).del) is missing)) and (((iptb.d).uName) = "user1@email.com”))"
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
]

2

btw, I’m using Couchbase 4.5 on the testing

3

MISSING values are not indexed, and so your query is scanning all users.

Tyr the following index instead:

CREATE INDEX idx_user_test ON iptb( t, d.uName, d.del ) WHERE t = “user”;