N1QL query count si so slow

SQL++
1

Hi All,

I’m using Couchbase 6.5.1 CE
and trying to do simple select query count:

SELECT COUNT(*)
FROM myBucket myTable
WHERE myTable.SRC_TABLE_NAME = ‘myTable’
AND myTable.flag =‘T’
AND myTable.valid <> ‘2’
;

result is 42.7 s
with total data in myBucket is 20 mio
and the query count result is 5.999.199

This is my index:

CREATE INDEX idx_count_test ON myBucket(flag,num,to_number(amount)) PARTITION BY HASH((meta().id)) WHERE ((SRC_TABLE_NAME = ‘myTable’) and (not (valid = ‘2’)))

This is the explain plan:

{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan3”,
“as”: “myTable”,
“covers”: [
“cover ((myTable.flag))”,
“cover ((myTable.num))”,
“cover (to_number((myTable.amount)))”,
“cover ((meta(myTable).id))”
],
“filter_covers”: {
“cover ((myTable.SRC_TABLE_NAME))”: “myTable”,
“cover ((not ((myTable.valid) = "2")))”: true
},
“index”: “idx_count_test”,
“index_id”: “a8fc937bc85358e0”,
“index_projection”: {
“entry_keys”: [
0
]
},
“keyspace”: “myBucket”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “"T"”,
“inclusion”: 3,
“low”: “"T"”
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(((cover ((myTable.SRC_TABLE_NAME)) = "myTable") and (cover ((myTable.flag)) = "T")) and cover ((not ((myTable.valid) = "2"))))”
},
{
“#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”
}
]
}
}
]
}

How to speed up this query?
I have tried using partition but still too slow

Thanks

2

If CE, The plan seems right. It need to scan Index and count through items. Not sure how you able to create partition index on CE.
If EE it should have used index aggregation.

3

Hi @vsr1 ,

Yes, sorry Ive used EE in non prod env to test it first.
what is index aggregation? how to use that?

4

Due to NOT in index where clause it is not using index aggregation in EE, use trick query and index.

CREATE INDEX idx_count_test ON default(flag,num,to_number(amount)) PARTITION BY HASH((meta().id)) WHERE ((SRC_TABLE_NAME = "myTable") and (valid != "2") = true );

EXPLAIN SELECT COUNT(1)
FROM default myTable
WHERE myTable.SRC_TABLE_NAME = "myTable"
AND (myTable.valid !="2") = true AND myTable.flag ="T";