Couchbase count query is so slow

Hi everyone,

My database query works very fast without using count query command. I use index and index works very well. It brings me data very fast. However, when I use something

SELECT COUNT(*) AS count … It works very slow. It lasts like 1 minute and there is no index for count. I have 415.000 records in my database.

What is the solution of this ? I think index does not work with a count ? Any advice please, how can I achieve this?

Please provide complete query and EXPLAIN. And corresponding index.


https://blog.couchbase.com/understanding-index-grouping-aggregation-couchbase-n1ql-query/

SELECT app.*, META(app).id AS id FROM app WHERE ( deleted = FALSE OR deleted IS MISSING ) AND _class = “com.myexample.app.device.data.model.DeviceEntity” AND appId = “something” AND dpnguage = “somelanguage” LIMIT 100 OFFSET 0

This query works very well and fast…Response time smaller than 100ms. However

SELECT COUNT(*) AS count FROM app WHERE ( deleted = FALSE OR deleted IS MISSING ) AND _class = “com.myexample.app.device.data.model.DeviceEntity” AND appId = “something” AND dpnguage = “somelanguage” LIMIT 100 OFFSET 0

This query works so slow. This is because of count command. Response time was like around 1 min.

I am doing query from couchbase UI. Normally I use CrudRepository in my Java/Spring application.

Indexes

  • #primary
  • class_appId_idx on ["_class","appId"]
  • class_appId_deleted_idx on ["_class","appId"] where ((deleted = false) or (deleted is missing))

First query you have pagination (LIMIT 100 OFFSET 0) without any ORDER. Query is finished when the limit condition is reached.

In second query you have the Aggregates without any group by. Query needs produce all qualified items and aggregate it. Eventually produces 1 item. It takes time

CREATE INDEX ix1 ON app(`_class`,`appId`, `dpnguage`) WHERE IFMISSING(deleted,false) = false;

SELECT COUNT(1) AS cnt FROM app
WHERE   IFMISSING(deleted,false) = false AND
 _class = "com.myexample.app.device.data.model.DeviceEntity" AND 
 `appId`  = "something" AND dpnguage = "somelanguage";

SELECT *, META().id FROM app
WHERE   IFMISSING(deleted,false) = false AND
 _class = "com.myexample.app.device.data.model.DeviceEntity" AND 
 `appId`  = "something" AND dpnguage = "somelanguage"
OFFSET 0 
LIMIT 100;

Hımmm I think this is not worked.

With dpnguage same as 1 min. I tried without dpnguage and it worked like 30 second. Still I need to reduce :confused:

And I should note that, I am very beginner on couchbase

Please post the EXPLAIN

Indexes
app.class_appId_idx app.ix1

Buckets
app

Fields
app.deleted app._class app.appId app.dp.language app.dp

In UI Press EXPLAIN Button. Then got to Plan Text Tab and paste info from there.

{
“plan”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “IntersectScan”,
“scans”: [
{
#operator”: “IndexScan3”,
“index”: “class_appId_idx”,
“index_id”: “8719368ebde1352d”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “appconnect”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “"com. myexample.appconnect.device.data.model.DeviceEntity"”,
“inclusion”: 3,
“low”: “"com. myexample.appconnect.device.data.model.DeviceEntity"”
},
{
“high”: “"something"”,
“inclusion”: 3,
“low”: “"something"”
}
]
}
],
“using”: “gsi”
},
{
#operator”: “IndexScan3”,
“index”: “ix1”,
“index_id”: “9082fb0c7323a3bd”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “appconnect”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “"com. myexample.appconnect.device.data.model.DeviceEntity"”,
“inclusion”: 3,
“low”: “"com. myexample.appconnect.device.data.model.DeviceEntity"”
},
{
“high”: “"something"”,
“inclusion”: 3,
“low”: “"something"”
}
]
}
],
“using”: “gsi”
}
]
},
{
#operator”: “Fetch”,
“keyspace”: “appconnect”,
“namespace”: “default”
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “Filter”,
“condition”: “((((ifmissing((appconnect.deleted), false) = false) and ((appconnect._class) = "com. myexample.appconnect.device.data.model.DeviceEntity")) and ((appconnect.appId) = "something")) and (((appconnect.dp).language) = "tr"))”
},
{
#operator”: “InitialGroup”,
“aggregates”: [
“count(1)”
],
“group_keys”:
}
]
}
},
{
#operator”: “IntermediateGroup”,
“aggregates”: [
“count(1)”
],
“group_keys”:
},
{
#operator”: “FinalGroup”,
“aggregates”: [
“count(1)”
],
“group_keys”:
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “cnt”,
“expr”: “count(1)”
}
]
},
{
#operator”: “FinalProject”
}
]
}
}
]
},
“text”: "SELECT COUNT(1) AS cnt FROM appconnect\nWHERE IFMISSING(deleted,false) = false AND\n _class = "com. myexample.appconnect.device.data.model.DeviceEntity" AND \n appId = "something" AND dp.language = "tr";"something

The filed you are using is dp.language and previous post has dpnguage = “somelanguage”
The following index is fixed that

CREATE INDEX ix1 ON app(_class,appId, dp.language) WHERE IFMISSING(deleted,false) = false;

Yes I know, I tried with that way. There was some typo but in query I wrote appropriately. Same result 30 second. Can not be reduced more ?

It looks like you don’t have index could you paste the index definition.
The Plan should look like below.

CREATE INDEX ix2 ON default(`_class`,`appId`, dp.language) WHERE IFMISSING(deleted,false) = false;
SELECT COUNT(1) AS cnt FROM default 
WHERE IFMISSING(deleted,false) = false AND
_class = "com. myexample.appconnect.device.data.model.DeviceEntity" AND 
appId = "whitelabel" AND dp.language = "tr";
{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan3",
        "covers": [
          "cover ((`default`.`_class`))",
          "cover ((`default`.`appId`))",
          "cover (((`default`.`dp`).`language`))",
          "cover ((meta(`default`).`id`))",
          "cover (count(1))"
        ],
        "filter_covers": {
          "cover (ifmissing((`default`.`deleted`), false))": false
        },
        "index": "ix2",
        "index_group_aggs": {
          "aggregates": [
            {
              "aggregate": "COUNT",
              "expr": "1",
              "id": 4,
              "keypos": -1
            }
          ]
        },
        "index_id": "1252bfe73be75581",
        "index_projection": {
          "entry_keys": [
            4
          ]
        },
        "keyspace": "default",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"com. myexample.appconnect.device.data.model.DeviceEntity\"",
                "inclusion": 3,
                "low": "\"com. myexample.appconnect.device.data.model.DeviceEntity\""
              },
              {
                "high": "\"whitelabel\"",
                "inclusion": 3,
                "low": "\"whitelabel\""
              },
              {
                "high": "\"tr\"",
                "inclusion": 3,
                "low": "\"tr\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "as": "cnt",
                  "expr": "cover (count(1))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT COUNT(1) AS cnt FROM default \nWHERE IFMISSING(deleted,false) = false AND\n_class = \"com. myexample.appconnect.device.data.model.DeviceEntity\" AND \nappId = \"whitelabel\" AND dp.language = \"tr\";"
}

26

Below the ix1

Definition
CREATE INDEX ix1 ON appconnect(_class,appId,dp.language) WHERE (ifmissing(deleted, false) = false)

Try this

   SELECT COUNT(1) AS cnt FROM appconnect USE INDEX (ix1)
    WHERE IFMISSING(deleted,false) = false AND
    _class = "com.myexample.appconnect.device.data.model.DeviceEntity" AND  
    appId = "whitelabel" AND dp.language = "tr";

It took 1 min. :confused:

success | elapsed: 1.00m | execution: 1.00m | count: 1 | size: 37

Hum. What version of couchbase and can you expand index definition and post the screen shot again.
You can do EXPLAIN on latest query and see if it only uses ix1 and uses covers

Sure:

community-6.0.0 ( Can it be because of it is community version ? )

My last query EXPLAIN:

{
“plan”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “IndexScan3”,
“index”: “ix1”,
“index_id”: “9082fb0c7323a3bd”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “appconnect”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““com.myexample.appconnect.device.data.model.DeviceEntity””,
“inclusion”: 3,
“low”: ““com.myexample.appconnect.device.data.model.DeviceEntity””
},
{
“high”: ““something””,
“inclusion”: 3,
“low”: ““something””
}
]
}
],
“using”: “gsi”
},
{
#operator”: “Fetch”,
“keyspace”: “appconnect”,
“namespace”: “default”
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “Filter”,
“condition”: “((((ifmissing((appconnect.deleted), false) = false) and ((appconnect._class) = “com.myexample.appconnect.device.data.model.DeviceEntity”)) and ((appconnect.appId) = “something”)) and (((appconnect.dp).language) = “tr”))”
},
{
#operator”: “InitialGroup”,
“aggregates”: [
“count(1)”
],
“group_keys”:
}
]
}
},
{
#operator”: “IntermediateGroup”,
“aggregates”: [
“count(1)”
],
“group_keys”:
},
{
#operator”: “FinalGroup”,
“aggregates”: [
“count(1)”
],
“group_keys”:
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “cnt”,
“expr”: “count(1)”
}
]
},
{
#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT COUNT(1) AS cnt FROM appconnect USE INDEX (ix1)\nWHERE IFMISSING(deleted,false) = false AND\n_class = “com.myexample.appconnect.device.data.model.DeviceEntity” AND\nappId = “something” AND dp.language = “tr”;”
}

There is issue with ix1 please drop and re-create it. If language is sub object inside dp the back ticks needs its own.

CREATE INDEX  `ix1`  ON  `appconnect` ( `_class` , `appId` , `dp`.`language` ) 
WHERE (ifmissing( `deleted` , false) = false);

If you want you can create the following index and drop other two indexes.

CREATE INDEX  `ix1`  ON  `appconnect`
 ( `_class` , `appId`, ifmissing( `deleted` , false) , `dp`.`language` ) ;

Resources that can help you
https://blog.couchbase.com/a-guide-to-n1ql-features-in-couchbase-5-5-special-edition/
https://blog.couchbase.com/category/n1ql-query/

1 Like

Omg, it returned 18 sec now rather than 1 min.:thinking: