Indexing on Sub Document

SQL++
1

I have a sub document in my main document in a format like this.

“assetId”: {
“assetNumber”: “METHV 804”,
“id”: “asset::4919340”
},

Sometimes I have list of “Id” values and I need to search for the document through that list. I use following n1QL query for it

select t.* from ic_v10_smalldemo as t where type=‘tenantPeriodicSchedule’ and t.isDeleted=false and _sync.rev is not null and t.assetId.id in [‘asset::4919439’,‘asset::4919340’,‘asset::4919338’,‘asset::4919336’,‘asset::4919334’,‘asset::4919332’,‘asset::1568531’,‘asset::1568527’]

Which takes around 3 seconds to complete. I tried adding an Index on the assetId.Id field with following n1ql

CREATE INDEX tenantPeriodicSchedule_assetId_idx ON ic_v10_smalldemo(assetId.id) WHERE (type = “tenantPeriodicSchedule”)

however the query is not following this index. Am I using the right syntax to create an Index on the sub document?

What is the efficient way to query when I have list of values and I wanted to filter that with the sub document?

2

It should have used the index. What is CB version. please post the EXPLAIN

3

Hi, I check this again today and it took the index without any issues…query responds within 40ms! I did not updated anything since my last work and when checked in again it works. I encountered such behavior few times things which don’t work starts working after sometime. not sure what could be the reason.

Here is the explain which considers index now and works well
{
“requestID”: “b04510c5-f349-4236-8da8-a496d359c932”,
“clientContextID”: “bc130360-f846-435d-b1fb-1101e59e0615”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IntersectScan”,
“scans”: [
{
"#operator": “IndexScan”,
“index”: “tenantPeriodicSchedule_assetId_idx”,
“index_id”: “3c4a1dc1fa02e82”,
“keyspace”: “ic_v10_smalldemo”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“asset::4901352"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4901352"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4901356"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4901356"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4901373"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4901373"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4904299"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4904299"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4904302"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4904302"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4907422"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4907422"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4916434"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4916434"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4916441"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4916441"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4916442"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4916442"”
]
}
},
{
“Range”: {
“High”: [
"“asset::4916443"”
],
“Inclusion”: 3,
“Low”: [
"“asset::4916443"”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexScan”,
“index”: “tenantPeriodicSchedule_isDeleted_idx”,
“index_id”: “15ef58f01e144459”,
“keyspace”: “ic_v10_smalldemo”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“false”
],
“Inclusion”: 3,
“Low”: [
“false”
]
}
}
],
“using”: “gsi”
}
]
},
{
"#operator": “Fetch”,
“as”: “t”,
“keyspace”: “ic_v10_smalldemo”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((((t.type) = “tenantPeriodicSchedule”) and ((t.isDeleted) = false)) and (((t._sync).rev) is not null)) and (((t.assetId).id) in [“asset::4916443”, “asset::4916442”, “asset::4916441”, “asset::4916434”, “asset::4907422”, “asset::4904302”, “asset::4904299”, “asset::4901373”, “asset::4901356”, “asset::4901352”]))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “_id”,
“expr”: “(meta(t).id)”
},
{
“expr”: “(t.assetId)”
},
{
“expr”: “(t.nextFillDate)”
},
{
“expr”: “(t.form)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “select meta(t).id _id, t.assetId, t.nextFillDate, t.form from ic_v10_smalldemo as t\r\n where type=‘tenantPeriodicSchedule’ and t.isDeleted=false and _sync.rev is not null and t.assetId.id in [‘asset::4916443’,‘asset::4916442’,‘asset::4916441’,‘asset::4916434’,‘asset::4907422’,‘asset::4904302’,‘asset::4904299’,‘asset::4901373’,‘asset::4901356’,‘asset::4901352’]”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “49.002ms”,
“executionTime”: “49.002ms”,
“resultCount”: 1,
“resultSize”: 10044
}
}

4

The following is right Index for your query and which avoids InterSectScan.

CREATE INDEX tenantPeriodicSchedule_assetId_idx ON ic_v10_smalldemo(assetId.id,isDeleted,_sync.rev) WHERE (type = “tenantPeriodicSchedule”);

To avoid Fetch and perform covering scan use the following index.

CREATE INDEX tenantPeriodicSchedule_assetId_idx ON ic_v10_smalldemo(assetId.id,isDeleted,_sync.rev,nextFillDate,form) WHERE (type = “tenantPeriodicSchedule”);

1 Like
5

Just curious, is it a good idea to add covering index for avoid fetch? I have been asking few questions here and I am being advised to add covering index every time. I have large database with large scale application contianing thousands of different queries. If I keep adding covering index to avoid fetch my index files would become large and at some point even larger than database! Let me know if I am thinking this right.

6

It depends. If the number of index keys and size of keys are big fetch might be better. You can try both and see which one perform better and based on that you can choose.