How N1QL Query is working without appropriate secondary index in 6.0.3

SQL++
, ,
1

Hi All,

Query1 is having one field in where clause f.Product.siteId , that is not present in the secondary index Index1 but still, Index1 is used for Query1 but not for Query2.
Query1 is having one field in where clause that is present in Index1, but Query2 doesn’t have anything common with Index1

Can someone please explain, how this Index is working and what is behind Couchbase mechanism used to resolve this query?

Index1 -
CREATE INDEX idx_bucket1 ON bucket(Product.Id,Product.p_Id)

Query 1 -
SELECT * FROM bucket AS f WHERE LOWER(f.Product.Id) = ‘x’ AND f.Product.siteId =‘Y’

Query 2-
SELECT * FROM bucket AS f WHERE f.Product.siteId =‘Y’

Explain for query - 1
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan3”,
“as”: “f”,
“index”: “idx_bucket1”,
“index_id”: “64f505705132ec25”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “bucket”,
“namespace”: “default”,
“spans”: [
{
“range”: [
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“as”: “f”,
“keyspace”: “bucket”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((lower(((f.Product).Id)) = “x”) and (((f.Product).siteId) = “Y”))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT * FROM bucket AS f WHERE LOWER(f.Product.Id) = ‘x’ AND f.Product.siteId =‘Y’”
}

Any help would be greatly appreciated.

Regards,
Naveen Nisad

2

Cocuchbase indexes gloabal indexes. As there is no schema for JSON. i.e fields might be MISSING from document to document. Bucket can have any type of documents. To minimize the index size indexer only indexes when leading index key value is present in the document. Due to that reason query predicate must depend on the leading index key to qualify index otherwise it result in skipping documents due to that reason it will not qualify that index.

Q1) Query has leading index key of index1 so it uses. f.Product.siteId porting of predicate applied after document fetch.
Q2) Product.siteId doesn’t qualify.

3

thanks for reply,
In my case I have around 10 fields in document that I can’t Index (because of Random combination on fields are allowed in query) but by using Index1 with leading key I’m able to use all 10 fields in where clause, with any combination.

Although for frequent fields in queries, added them in Index (to increase the leading keys for finding the qualifying Index), and for those fields are less frequent in queries as per business, I didn’t create any Index for them.

It is getting hard for me to understand Performance difference with 50M documents in bucket.
Will this Strategy be performance Proof?

4

Also checkout FTS index.

5

I went through those links for understanding but still for my use case I was unanswered.

Better I will test this with Load tool and understand performance impact.