Order by Desc is very slow, takes more than 7 secs

Couchbase Server
, ,
1

Could you help resolving the slowness issue?

Below are the details:

Index :

CREATE INDEX devicereading_ARR_INDEX ON System_Module(type,gatewayId,areaName,locationName,time,created DESC,ALL DISTINCT ARRAY {sensor.tagId,sensor.zone,sensor.temperature,sensor.humidity } FOR sensor IN sensorTags END) WHERE (type = “com.bonbloc.api.domain.DeviceReading”)

Query :

select s.gatewayId,s.areaName,s.locationName,sensor.tagId,sensor.zone,sensor.temperature,sensor.humidity,s.time,s.created from System_Module s
UNNEST sensorTags As sensor where s.type=“com.bonbloc.api.domain.DeviceReading” order by s.created DESC limit 100

EXPLAIN:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "DistinctScan",
            "scan": {
              "#operator": "IndexScan3",
              "as": "s",
              "index": "devicereading_ARR_INDEX",
              "index_id": "712d01fd94575985",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "System_Module",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"com.bonbloc.api.domain.DeviceReading\"",
                      "inclusion": 3,
                      "low": "\"com.bonbloc.api.domain.DeviceReading\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            }
          },
          {
            "#operator": "Fetch",
            "as": "s",
            "keyspace": "System_Module",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Unnest",
                  "as": "sensor",
                  "expr": "(`s`.`sensorTags`)"
                }
              ]
            }
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "((`s`.`type`) = \"com.bonbloc.api.domain.DeviceReading\")"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "(`s`.`gatewayId`)"
                    },
                    {
                      "expr": "(`s`.`areaName`)"
                    },
                    {
                      "expr": "(`s`.`locationName`)"
                    },
                    {
                      "expr": "(`sensor`.`tagId`)"
                    },
                    {
                      "expr": "(`sensor`.`zone`)"
                    },
                    {
                      "expr": "(`sensor`.`temperature`)"
                    },
                    {
                      "expr": "(`sensor`.`humidity`)"
                    },
                    {
                      "expr": "(`s`.`time`)"
                    },
                    {
                      "expr": "(`s`.`created`)"
                    }
                  ]
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Order",
        "limit": "100",
        "sort_terms": [
          {
            "desc": true,
            "expr": "(`s`.`created`)"
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "100"
      },
      {
        "#operator": "FinalProject"
      }
    ]
  },
  "text": "select s.gatewayId,s.areaName,s.locationName,sensor.tagId,sensor.zone,sensor.temperature,sensor.humidity,s.time,s.created from System_Module s \r\nUNNEST sensorTags As sensor where s.type=\"com.bonbloc.api.domain.DeviceReading\" order by s.created DESC limit 100"
}
2 
CREATE INDEX devicereading_ARR_INDEX ON System_Module (created DESC, gatewayId, areaName, locationName, time,
           ARRAY {sensor.tagId, sensor.zone, sensor.temperature, sensor.humidity } FOR sensor IN sensorTags END)
WHERE (type = "com.bonbloc.api.domain.DeviceReading")

Query :

SELECT s.gatewayId, s.areaName, s.locationName, s.time, s.created,
ARRAY {sensor.tagId, sensor.zone, sensor.temperature, sensor.humidity } FOR sensor IN sensorTags END AS sensors
FROM System_Module AS s
WHERE s.type="com.bonbloc.api.domain.DeviceReading" AND s.created IS NOT NULL
ORDER BY s.created DESC
LIMIT 100;

OR

SELECT  s.gatewayId, s.areaName, s.locationName, s.time, s.created, sensor.*
FROM ( SELECT s.gatewayId, s.areaName, s.locationName, s.time, s.created,
              ARRAY {sensor.tagId, sensor.zone, sensor.temperature, sensor.humidity } FOR sensor IN sensorTags END AS sensors
       FROM System_Module AS s
       WHERE s.type="com.bonbloc.api.domain.DeviceReading" AND s.created IS NOT NULL
       ORDER BY s.created DESC
       LIMIT 100 ) AS s
UNNEST s.sensors AS sensor
ORDER BY s.created DESC
LIMIT 100;

Rule #7

3

@vsr1, Thank you so much for your response, execution is <20 ms.