Why my count queries are slow?

Couchbase Server
, ,
1

I have this index

CREATE INDEX `test_field1_field2_index` ON `qtest`.`qscope`.`qcoll`(`field1`,`field2`);

And I want to execute these 2 query, but they take too much time. Can you help me about this.
You can find query, explain and information from system:completed_requests below.

Query1

select count(*) from qtest.qscope.qcoll WHERE field1 = 1916

Explain1

{
    "plan": {
        "#operator": "Sequence",
        "~children": [
            {
                "#operator": "IndexCountScan2",
                "bucket": "qtest",
                "covers": [
                    "cover ((`qcoll`.`field1`))",
                    "cover ((`qcoll`.`field2`))",
                    "cover ((meta(`qcoll`).`id`))"
                ],
                "index": "test_field1_field2_index",
                "index_id": "6c2b9425f5253f1",
                "keyspace": "qcoll",
                "namespace": "default",
                "scope": "qscope",
                "spans": [
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "1916",
                                "inclusion": 3,
                                "index_key": "`field1`",
                                "low": "1916"
                            }
                        ]
                    }
                ],
                "using": "gsi"
            },
            {
                "#operator": "IndexCountProject",
                "result_terms": [
                    {
                        "expr": "count(*)"
                    }
                ]
            }
        ]
    }
}

InfoFromCompletedRequests1

{
  "phaseOperators": {
    "project": 1,
    "stream": 1,
    "authorize": 1,
    "indexCount": 1
  },
  "clientContextID": "30f6786d-3a57-42f4-9ca0-bb97c7ec6f00",
  "resultCount": 1,
  "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36 (Couchbase Query Workbench)",
  "serviceTime": "2m15.743374535s",
  "n1qlFeatCtrl": 127,
  "users": "ejtestuser",
  "requestTime": "2023-08-22T17:49:52.874+03:00",
  "scanConsistency": "unbounded",
  "requestId": "6f3fdf70-bb79-4a49-ba48-ce4e736bbe40",
  "resultSize": 27,
  "statementType": "SELECT",
  "statement": "select count(*) from qtest.qscope.qcoll WHERE account_id = 1916",
  "state": "completed",
  "phaseTimes": {
    "stream": "51.731µs",
    "indexCount": "2m15.741103272s",
    "project": "47.373µs",
    "run": "2m15.742000098s",
    "parse": "989.879µs",
    "authorize": "23.051µs",
    "instantiate": "19.34µs",
    "plan": "339.948µs"
  },
  "errorCount": 0,
  "errors": [],
  "elapsedTime": "2m15.743447219s",
  "remoteAddr": "10.154.145.98:40548"
}

Query2

select count(*) from qtest.qscope.qcoll WHERE field1 = 626321 AND field2 in [28,77,56,151,29,46,17,66,25,26,55,79,33,24,35,21,54,84,19,69,40,150,5,53,41,147,59,80,58,82,81,47,9,31,148,4,13,149,64,65,20,8,49,18,36,3,153,34,32,45,78,178,76,39,57,61,1,51,30,7,68,75,50];

Explain2

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexCountScan2",
        "bucket": "qtest",
        "covers": [
          "cover ((`qcoll`.`field1`))",
          "cover ((`qcoll`.`field2`))",
          "cover ((meta(`qcoll`).`id`))"
        ],
        "index": "test_field1_field2_index",
        "index_id": "6c2b9425f5253f1",
        "keyspace": "qcoll",
        "namespace": "default",
        "scope": "qscope",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "1",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "1"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "3",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "3"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "4",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "4"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "5",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "5"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "7",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "7"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "8",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "8"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "9",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "9"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "13",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "13"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "17",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "17"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "18",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "18"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "19",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "19"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "20",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "20"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "21",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "21"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "24",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "24"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "25",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "25"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "26",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "26"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "28",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "28"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "29",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "29"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "30",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "30"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "31",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "31"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "32",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "32"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "33",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "33"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "34",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "34"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "35",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "35"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "36",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "36"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "39",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "39"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "40",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "40"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "41",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "41"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "45",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "45"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "46",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "46"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "47",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "47"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "49",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "49"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "50",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "50"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "51",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "51"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "53",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "53"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "54",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "54"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "55",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "55"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "56",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "56"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "57",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "57"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "58",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "58"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "59",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "59"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "61",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "61"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "64",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "64"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "65",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "65"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "66",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "66"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "68",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "68"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "69",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "69"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "75",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "75"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "76",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "76"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "77",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "77"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "78",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "78"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "79",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "79"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "80",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "80"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "81",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "81"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "82",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "82"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "84",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "84"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "147",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "147"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "148",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "148"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "149",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "149"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "150",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "150"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "151",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "151"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "153",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "153"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "626321",
                "inclusion": 3,
                "index_key": "`field1`",
                "low": "626321"
              },
              {
                "high": "178",
                "inclusion": 3,
                "index_key": "`field2`",
                "low": "178"
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "IndexCountProject",
        "result_terms": [
          {
            "expr": "count(*)"
          }
        ]
      }
    ]
  }
}

InfoFromCompletedRequests2

{
  "phaseOperators": {
    "project": 1,
    "stream": 1,
    "authorize": 1,
    "indexCount": 1
  },
  "clientContextID": "62fba7bc-7683-4f63-a978-f706c16a7ddc",
  "resultCount": 1,
  "queryContext": "`default`:`qtest`.`qscope`",
  "userAgent": "couchbase-java/3.4.0 (Linux 3.10.0-1062.el7.x86_64 amd64; OpenJDK 64-Bit Server VM 17.0.2+8-86)",
  "serviceTime": "20.956243897s",
  "n1qlFeatCtrl": 127,
  "users": "ejtestuser",
  "requestTime": "2023-08-22T17:21:25.511+03:00",
  "scanConsistency": "unbounded",
  "requestId": "fa5680a0-af24-4e17-8a12-af55938a24fc",
  "resultSize": 11,
  "statementType": "SELECT",
  "statement": "select count(*) from qtest.qscope.qcoll WHERE field1 = 626321 AND field2 in [28,77,56,151,29,46,17,66,25,26,55,79,33,24,35,21,54,84,19,69,40,150,5,53,41,147,59,80,58,82,81,47,9,31,148,4,13,149,64,65,20,8,49,18,36,3,153,34,32,45,78,178,76,39,57,61,1,51,30,7,68,75,50]",
  "state": "completed",
  "phaseTimes": {
    "stream": "60.352µs",
    "indexCount": "20.94982403s",
    "project": "23.42µs",
    "run": "20.950068008s",
    "parse": "3.571141ms",
    "authorize": "21.73µs",
    "instantiate": "33.771µs",
    "plan": "2.536573ms"
  },
  "errorCount": 0,
  "errors": [],
  "elapsedTime": "20.956344595s",
  "remoteAddr": "10.154.145.18:62363"
}
2

What is your Couchbase version? Current versions will not be generating IndexCountScan2 operators rather pushing aggregates down to the indexer.

3

Community Edition 7.1.1

4

Ah… OK - Sorry, I forgot the limitations w.r.t. push-down with Community.

Do you find this performance - notably the the 2m15s index scan time - repeatable or is it on the first run only?

How many documents do you have in your bucket?

Given the two fields are just integers, and the filter (first statement) is pretty selective (only 27 results) it ought to be somewhat faster.

What is the layout of your cluster? All services on a single node? Is the machine under memory pressure that may perhaps be causing swapping etc. ? (i.e. Is there possibly an external influence on the performance of the cluster?)

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
6

Do you find this performance - notably the the 2m15s index scan time - repeatable or is it on the first run only?

No, if I execute same query consecutively its returning in milliseconds. But after a while same things happened.

How many documents do you have in your bucket?

Around 10 millions.

What is the layout of your cluster? All services on a single node? Is the machine under memory pressure that may perhaps be causing swapping etc. ? (i.e. Is there possibly an external influence on the performance of the cluster?)

After your question I’ve checked memory usage and index servie is almost full. Is it could be the problem?

image
image1209×344 27.7 KB

There is 1 node, all services running on a single nod and there are 2 buckets.
Ram Quotas are; Data 4.01GiB | Index 512MiB | Search 512MiB

I am working on the test cluster. There are not much read or write request. Also the bucket that I am using is just for me, there are no other requests, just me.

7

That could well influence things. It does sound/seem like it is a problem with initial disk access, priming memory. Once suitably warmed up it seems to be at the levels we’d expect.

8

I’ve increased the memory and now there is space in ram quota, it’s not full.

image
image1187×332 27.7 KB

It became almost 2 times faster but

select count(*) from qtest.qscope.qcoll WHERE field1 = 1916

this query still take more than 1 minute so I think this is only a part of the solution.

9

On start-up there will be priming of the services. I believe you will never reliably get instantaneous first-execution-immediately-after-restart performance. It should be expected that once the services are “warm” you’ll get the necessary performance.

I’ve managed to once get a 30s first-run for myself with 10m documents and similar memory to your original (I assumed 2 GiB per bucket). But this was in the “plan” phase - entirely waiting for the indexer service to finish bootstrapping and respond. This could be worse if the I/O performance was worse. The IndexCountScan2 has always performed well.

After a restart, are you running the statement immediately or do you wait a few minutes then run the first statement? (If not, then if you do, does it make a difference?)

Edit: Also, when it happens again some time after the first run, is it as severe or is it just slower than average but not as slow as the first run? Does this happen when you’ve spent a long time with other queries using other indices? (Is other workload evicting the index and it has to be reloaded when you get back to it?)

10

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.