ORDER BY is slow, have DESC in index

SQL++
1

Execution time of ORDER BY clause is around 6 seconds. Total docs are just 12K.
Couchbase 6.0.0

success | elapsed: 6.81s | execution: 6.81s | count: 10 | size: 4869

{
    "bktXXX": {
      "code": "123",
      "createdAt": 1577177553797,
      "from": "XXX",
      "ip": "XXX",
      "status": 0,
      "type": "unknown"
    }
  },

INDEX

CREATE INDEX `idx_unknown` ON `bktXXX`(`status`, `createdAt` DESC) WHERE (`type` = "unknown")

QUERY

SELECT * FROM `bktXXX` USE INDEX (idx_unknown USING GSI) WHERE status = 0 AND createdAt IS NOT MISSING AND type="unknown" ORDER BY createAt DESC LIMIT 10 OFFSET 0

EXPLAIN

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "IndexScan3",
            "index": "idx_unknown",
            "index_id": "85927b3e4d75887a",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "bktList",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "0",
                    "inclusion": 3,
                    "low": "0"
                  },
                  {
                    "inclusion": 1,
                    "low": "null"
                  }
                ]
              }
            ],
            "using": "gsi"
          },
          {
            "#operator": "Fetch",
            "keyspace": "bktList",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "((((`bktList`.`status`) = 0) and ((`bktList`.`createdAt`) is not missing)) and ((`bktList`.`type`) = \"unknown\"))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "self",
                      "star": true
                    }
                  ]
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Order",
        "limit": "10",
        "sort_terms": [
          {
            "desc": true,
            "expr": "(`bktList`.`createAt`)"
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "10"
      },
      {
        "#operator": "FinalProject"
      }
    ]
  },
  "text": "SELECT * FROM `bktList` USE INDEX (idx_unknown USING GSI) WHERE status = 0 AND createdAt IS NOT MISSING AND type=\"unknown\" ORDER BY createAt DESC LIMIT 10 OFFSET 0"
}

Can someone help idenitify whats making this query slow.

2

I think there is typo in ORDER BY.
Index key/document field createdAt vs ORDER BY createAt
It is not able exploit index order and producing all qualified items for sort.

1 Like
3

Ohh silly me. Thank you very much for pointing that out.

 success | elapsed: 38.61ms | execution: 38.49ms | count: 10 | size: 4848