Select Count(*) query is taking 45 seconds (I am using indexes)

SQL++
1

I need help to figure-out why my query is not using indexCountScan. I am using couchbase 4.0.0 version.
I have created below index:

CREATE INDEX indx_org ON ctmonitor_t(organizationName) USING GSI

My query is as below:

select count(*) from ctmonitor_t use index(indx_org) where organizationName!=‘DHRUV’;

I get below plan in explain:
cbq.exe> explain select count() from ctmonitor_t use index(indx_org) where organizationName!=‘DHRUV’;
{
“requestID”: “d265bbeb-efdd-4d1c-b00d-3cbbb5510333”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “UnionScan”,
“scans”: [
{
"#operator": “IndexScan”,
“index”: “indx_org”,
“keyspace”: “ctmonitor_t”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“DHRUV”"
],
“Inclusion”: 0,
“Low”: null
},
“Seek”: null
},
{
“Range”: {
“High”: null,
“Inclusion”: 0,
“Low”: [
"“DHRUV”"
]
},
“Seek”: null
}
],
“using”: “gsi”
}
]
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “ctmonitor_t”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “(not ((ctmonitor_t.organizationName) = “DHRUV”))”
},
{
"#operator": “InitialGroup”,
“aggregates”: [
"count()"
],
“group_keys”: []
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
“count()"
],
“group_keys”: []
},
{
"#operator": “FinalGroup”,
“aggregates”: [
"count()”
],
“group_keys”: []
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “count(*)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “2.0002ms”,
“executionTime”: “2.0002ms”,
“resultCount”: 1,
“resultSize”: 3727
}
}

As per my understanding couchbase should lookup indexes and give me a count without reading the documents on disk. But the server graphs show that it is reading the documents also.

2

In Addition:

If I use organizationName >’’ in where clause, I still see response in more than 45 secs.
Here is the explain for same:

cbq.exe> explain select count() from ctmonitor_t use index(indx_org) where organizationName>’’;
{
“requestID”: “9a31bcf7-04f0-401e-b62f-4228c6d00eb9”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “indx_org”,
“keyspace”: “ctmonitor_t”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: null,
“Inclusion”: 0,
“Low”: [
""""
]
},
“Seek”: null
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “ctmonitor_t”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “(”" \u003c (ctmonitor_t.organizationName))"
},
{
"#operator": “InitialGroup”,
“aggregates”: [
"count()"
],
“group_keys”: []
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
“count()"
],
“group_keys”: []
},
{
"#operator": “FinalGroup”,
“aggregates”: [
"count()”
],
“group_keys”: []
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “count(*)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “7.0007ms”,
“executionTime”: “7.0007ms”,
“resultCount”: 1,
“resultSize”: 2960
}
}

3

You need to use at least Couchbase 4.1, and preferably 4.5.1.

4

Hi Gerald,
Thanks for the response. I have downloaded current release version 4.5.1 and I am in process to setup. I will post my update on improvements as soon as things are ready for test.

Can you please send me the details on how to achieve the indexCountScan in queries while using GSI indexes.

Thanks,
Dhruv

5

Some of these articles discuss COUNT. N1QL Resources: Workshop, Articles and more

@keshav_m

6

Hi Gerald,

I installed couchbase 4.5.1 on two servers. All three service (Index/Data& Query) services running on both.
Data volume ~650K

I have below index:
CREATE INDEX idx_expiryTime ON ctmonitor_t(expiryTime)

My count query is:
select count(*) from ctmonitor_t where expiryTime>0;

Still I am getting response in 3.2 seconds. which seems too high. Can you please point me the area of improvement?

Explain:
cbq.exe> explain select count() from ctmonitor_t where expiryTime>0;
{
“requestID”: “0e639e8f-5a60-444f-a27f-6d97207be5e7”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexCountScan”,
“covers”: [
“cover ((ctmonitor_t.expiryTime))”,
“cover ((meta(ctmonitor_t).id))”
],
“index”: “idx_expiryTime”,
“index_id”: “90d56d512420312e”,
“keyspace”: “ctmonitor_t”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“Inclusion”: 0,
“Low”: [
“0”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexCountProject”,
“result_terms”: [
{
“expr”: "count()"
}
]
}
]
},
“text”: “select count(*) from ctmonitor_t where expiryTime\u003e0”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “1.0001ms”,
“executionTime”: “1.0001ms”,
“resultCount”: 1,
“resultSize”: 1417
}
}

7

Hi @dhruvdm, this COUNT performance will be fixed in the next major release, code-named Spock (probably 5.0, in 2017). @vsr1 @siri