Couchbase count query is so slow

alpcan.yildiz · December 19, 2018, 6:36pm

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?

vsr1 · December 19, 2018, 6:40pm

Please provide complete query and EXPLAIN. And corresponding index.

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

alpcan.yildiz · December 19, 2018, 6:44pm

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))

vsr1 · December 19, 2018, 6:48pm

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

vsr1 · December 19, 2018, 6:55pm

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;

alpcan.yildiz · December 19, 2018, 7:07pm

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

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

vsr1 · December 19, 2018, 8:22pm

Please post the EXPLAIN

alpcan.yildiz · December 19, 2018, 9:27pm

Indexes
app.class_appId_idx app.ix1

Buckets
app

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

vsr1 · December 19, 2018, 9:34pm

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

alpcan.yildiz · December 19, 2018, 9:37pm

{
“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

vsr1 · December 19, 2018, 9:40pm

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;

alpcan.yildiz · December 19, 2018, 9:41pm

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 ?

vsr1 · December 19, 2018, 9:43pm

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\";"
}

alpcan.yildiz · December 19, 2018, 9:46pm

Below the ix1

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

vsr1 · December 19, 2018, 9:51pm

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";

alpcan.yildiz · December 19, 2018, 9:57pm

It took 1 min.

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

vsr1 · December 19, 2018, 9:59pm

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

alpcan.yildiz · December 19, 2018, 10:04pm

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”;”
}

vsr1 · December 19, 2018, 10:07pm

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/

alpcan.yildiz · December 19, 2018, 10:16pm

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

Topic		Replies	Views
Slow count query SQL++	7	1504	December 19, 2018
Count query is very slow and returns 0 some times even with indexes and IndexCountScan SQL++ query , index	1	762	March 25, 2021
N1QL query count si so slow SQL++	3	785	January 26, 2022
Anyway to make this simple count + group query faster? SQL++	2	845	July 12, 2018
Queries take minutes with N1QL SQL++ n1ql	4	1058	October 10, 2017

Couchbase count query is so slow

Related topics