Hi Team,
I am using count sql for pagination. But its taking more than 2 seconds to provide output which is degrading performance. Could you please help me here?
When is use select count(*) as count from mybucket then output is faster. But when i use WHERE clause then is taking long time to respond.
My Actual Count SQL : select count(*) as count from mybucket where type=“JRN” and status = “FAILED”;
Couchbase Server = 6.0.1
Explain Output :
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan3”,
“covers”: [
“cover ((mybucket .type))”,
“cover ((mybucket .status))”,
“cover ((meta(mybucket ).id))”,
“cover (count())"
],
“index”: “count_idx”,
“index_group_aggs”: {
“aggregates”: [
{
“aggregate”: “COUNT”,
“expr”: “1”,
“id”: 3,
“keypos”: -1
}
]
},
“index_id”: “748301f0d8c70ef5”,
“index_projection”: {
“entry_keys”: [
3
]
},
“keyspace”: "mybucket ",
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““JRN””,
“inclusion”: 3,
“low”: ““JRN””
},
{
“high”: ““FAILED””,
“inclusion”: 3,
“low”: ““FAILED””
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “count”,
“expr”: "cover (count())”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “select count(*) as count from mybucket where type=“JRN” and status = “FAILED”;”
}
My Index : CREATE INDEX count_idx ON mybucket (type,status) WITH { “num_replica”:1 }