N1QL Query 2 million records very slow

SQL++
, ,
1

What happen query with the following N1QL query on 2 millon docements

Screen Shot 2564-01-15 at 18.56.32
Screen Shot 2564-01-15 at 18.56.321564×468 32.8 KB

Explain

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "PrimaryScan3",
            "index": "#primary-10-127-91-238",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "deliveries",
            "namespace": "default",
            "using": "gsi"
          },
          {
            "#operator": "Fetch",
            "keyspace": "deliveries",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "(((`deliveries`.`type`) = \"BOL\") and ((`deliveries`.`isExtraCid`) = true))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "self",
                      "star": true
                    }
                  ]
                },
                {
                  "#operator": "FinalProject"
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "1"
      }
    ]
  },
  "text": "select * from `deliveries` where type = \"BOL\" and isExtraCid = true limit 1"
}
2

Sample Document (remove some data due to character limit)

[
  {
    "deliveries": {
      "_class": "com.tesco.intl.deliveries.model.entity.Delivery",
      "countryCode": "TH",
      "createdBy": "svc-sce-intl",
      "createdTimeStamp": "2021-01-01T16:57:27.795754Z",
      "data": {
        "extraCids": [
          {
            "id": "50010000000000016591"
          },
          {
            "id": "00000009080481891955"
          },
          {
            "id": "5001MT00000000000386"
          },
          {
            "id": "50010000000000018101"
          }
        ],
        "pendingContainers": [],
        "receivedContainers": [
          {
            "id": "00000050010529228609"
          },
          {
            "id": "00000050010529228005"
          },
          {
            "id": "00000050010529228016"
          },
          {
            "id": "00000050010529228017"
          },
          {
            "id": "00000050010529228007"
          },
          {
            "id": "00000050010529227997"
          },
          {
            "id": "00000050010529228008"
          },
          {
            "id": "00000050010529228009"
          },
          {
            "id": "00000050010529228010"
          },
          {
            "id": "00000050010529228011"
          },
          {
            "id": "00000050010529228012"
          },
          {
            "id": "00000050010529228013"
          },
          {
            "id": "00000050010529228542"
          },
          {
            "id": "00000050010529228014"
          },
          {
            "id": "00000050010529228543"
          },
          {
            "id": "00000050010529228004"
          },
          {
            "id": "00000050010529228015"
          }
        ]
      },
      "deviceId": "SL_5001",
      "expectedDeliveryDate": "2021-01-02",
      "isAutoSubmitted": false,
      "isExpired": false,
      "isExtraCid": true,
      "isSoftDeleted": false,
      "lastUpdatedByUser": "Noppadol Tuidee",
      "lastUpdatedTimeStamp": "2021-01-01T16:57:27.795754Z",
      "lastUpdatedUserTimeStamp": "2021-01-02T04:24:03.899907Z",
      "rawData": {
        "data": "...........// remove some big raw data due to character limit //.............."
        "source": "STORELINE"
      },
      "status": "SUBMIT",
      "storeNumber": "5001",
      "submitedDate": "2021-01-02",
      "traceId": "082efba7-c605-4c67-a793-ee232b6f1877",
      "type": "BOL",
      "typeId": "09060003000769",
      "unknownBol": false,
      "updatedBy": "svc-sce-intl"
    }
  }
]
3

CREATE INDEX ix1 ON deliveries(isExtraCid) WHERE type = "BOL";

https://index-advisor.couchbase.com/indexadvisor/