Why my count queries are slow?

Couchbase Server
, ,
5

FWIW, this is a quick test I’ve just done. Single node with kv+index+query:

cbq> select ds_version();
{
    "requestID": "97895906-6e6f-4902-bc3f-ef1a7291422c",
    "signature": {
        "$1": "string"
    },
    "results": [
    {
        "$1": "7.1.1-3175-community"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "398.902µs",
        "executionTime": "331.12µs",
        "resultCount": 1,
        "resultSize": 44,
        "serviceLoad": 6
    }
}
cbq> select count(1) from qtest.qscope.qcoll where field1 is valued;
{
    "requestID": "267f066d-32cf-4a2d-97dc-4e1f3021a215",
    "signature": {
        "$1": "number"
    },
    "results": [
    {
        "$1": 500000
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "802.890514ms",
        "executionTime": "802.842135ms",
        "resultCount": 1,
        "resultSize": 28,
        "serviceLoad": 6
    }
}
cbq> select count(1) from qtest.qscope.qcoll WHERE field1 = 1916;
{
    "requestID": "8db1a04a-97aa-418a-b077-ec8b4584cfbe",
    "signature": {
        "$1": "number"
    },
    "results": [
    {
        "$1": 27
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.148979ms",
        "executionTime": "2.047218ms",
        "resultCount": 1,
        "resultSize": 24,
        "serviceLoad": 6
    }
}

Machine:

$ lscpu
Architecture:                    x86_64
CPU op-mode(s):                  32-bit, 64-bit
Byte Order:                      Little Endian
Address sizes:                   39 bits physical, 48 bits virtual
CPU(s):                          8
On-line CPU(s) list:             0-7
Thread(s) per core:              2
Core(s) per socket:              4
Socket(s):                       1
NUMA node(s):                    1
Vendor ID:                       GenuineIntel
CPU family:                      6
Model:                           142
Model name:                      Intel(R) Core(TM) i7-8565U CPU @ 1.80GHz
...
$ lsmem
RANGE                                  SIZE  STATE REMOVABLE  BLOCK
0x0000000000000000-0x000000006fffffff  1.8G online       yes   0-13
0x0000000100000000-0x0000000487ffffff 14.1G online       yes 32-144

Memory block size:       128M
Total online memory:    15.9G
Total offline memory:      0B

$ hwinfo --disk
30: PCI 00.0: 10600 Disk                                        
  [Created at block.245]
  Unique ID: wLCS.d+V8_oGKuiE
  Parent ID: svHJ.ZdVsUKdIF8E
  SysFS ID: /class/block/nvme0n1
  SysFS BusID: nvme0
  SysFS Device Link: /devices/pci0000:00/0000:00:1d.0/0000:03:00.0/nvme/nvme0
...

$ cat /etc/issue
Linux Mint 20.3 Una \n \l