Adaptive index question

SQL++
1

I want to use an adaptive index like this one :

CREATE INDEX stats_email_message_index
ON stats((distinct (pairs({
“date”: (((data).email).date),
“senderDomainId”: (((((data).email).sender).domain).id),
“recipientDomainId”: (((((data).email).recipient).domain).id),
“recipientMail”: ((((data).email).recipient).mail),
“senderMail”: ((((data).email).sender).mail),
“subject”: (((data).email).subject)}))))
WHERE (type = “email”)

For some reason I can never get the index been use even when querying for all values on the index.

explain select stats.*
from stats
use index(stats_email_message_index)
where data.email.date =0
and data.email.messageId =0
and data.email.sender.domain.id =""
and data.email.recipient.domain.id =""
and data.email.recipient.mail =""
and data.email.sender.mail = “”
and data.email.subject
and type = “email”

[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “stats”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “stats”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((((((((((stats.data).email).date) = 0) and ((((((stats.data).email).sender).domain).id) = “”)) and ((((((stats.data).email).recipient).domain).id) = “”)) and (((((stats.data).email).recipient).mail) = “”)) and (((((stats.data).email).sender).mail) = “”)) and ((((stats.data).email).subject) = “”)) and ((stats.type) = “email”))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “stats”,
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “select stats.*\nfrom stats\nuse index(stats_email_message_index)\nwhere data.email.date =0\nand data.email.sender.domain.id =”"\nand data.email.recipient.domain.id =""\nand data.email.recipient.mail =""\nand data.email.sender.mail = “”\nand data.email.subject = “”\nand type = “email”"
}
]

2

Index WHERE clause also needs to be part of the Index keys.

Also if you give different filed name form expression adaptive index may not pick for that key (example: “recipientDomainId”: (((((data).email).recipient).domain).id) as key data.email.recipient.domain.id ="" as prediacare )
Check limitations https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/adaptive-indexing.html

3

I am having issue with documentation on creating index…

If I try to create it like this with alias
CREATE INDEX stats_email_message_index ON stats(
(distinct (pairs({
type
,domain
,((data.email).date)
,((data.email).messageId)
,((data.email.recipient).mail)
,((data.email.recipient.domain).id)
,((data.email.sender).mail)
,((data.email.sender.domain).id)
,((data.email).subject)
})))
)
WHERE (type = “email”);

I end up with lesser fields on the index, looks like id and mail are repeated and just chose one random. This was created instead of all fields:

CREATE INDEX stats_email_message_index ON stats((distinct (pairs({“date”: ((data.email).date), “domain”: domain, “id”: ((((data.email).sender).domain).id), “mail”: (((data.email).sender).mail), “messageId”: ((data.email).messageId), “subject”: ((data.email).subject), “type”: type})))) WHERE (type = “email”)

Also this index never hits, … no idea why is adding this aliases… may be part of the issue. Any suggestion?

4

During object construction filed name inherits form last field name (example: data.email.recipient.domain.id, id is filed name)
If there is duplicates it will pick randomly.
Due to duplicates it not able to pick the index MB-29742