Couchbase 6, N1SQL Order by desc is taking long time

SQL++
, ,
1

I am using couchbase 6.0.1 and N1SQL order by desc is taking long time to provide result. With Order By its taking around 12s and if I remove the Order by then it taking only 9ms.

My Index and SQL and Explain are below. Could you please help me?

Index :
CREATE INDEX myindex ON mybucket(type,status,subStatus,startTimestamp,endTimestamp,failureReason,id,name,interaction,traceID,accountNumberToken,customerID,pcn) WHERE ((status = “FAILED”) and (type = “TYP”)) WITH { “defer_build”:true, “nodes”:[ “node1.ahx.my.com:8091”,“node2.ahx.my.com:8091:8091”,“node3.ahx.my.com:8091” ], “num_replica”:2 }

Query :
select id,status,subStatus,interaction,endTimestamp,failureReason,traceID,accountNumberToken,customerID,pcn,startTimestamp from mybucket where type=“TYP” and status = “FAILED” OFFSET 0 LIMIT 100;

Explain:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "IndexScan3",
            "covers": [
              "cover ((`mybucket`.`type`))",
              "cover ((`mybucket`.`status`))",
              "cover ((`mybucket`.`subStatus`))",
              "cover ((`mybucket`.`startTimestamp`))",
              "cover ((`mybucket`.`endTimestamp`))",
              "cover ((`mybucket`.`failureReason`))",
              "cover ((`mybucket`.`id`))",
              "cover ((`mybucket`.`name`))",
              "cover ((`mybucket`.`interaction`))",
              "cover ((`mybucket`.`traceID`))",
              "cover ((`mybucket`.`accountNumberToken`))",
              "cover ((`mybucket`.`customerID`))",
              "cover ((`mybucket`.`pcn`))",
              "cover ((meta(`mybucket`).`id`))"
            ],
            "filter_covers": {
              "cover ((`mybucket`.`status`))": "FAILED",
              "cover ((`mybucket`.`type`))": "TYP"
            },
            "index": "myindex",
            "index_id": "c273e7c8b2214cbb",
            "index_projection": {
              "entry_keys": [
                0,
                1,
                2,
                3,
                4,
                5,
                6,
                8,
                9,
                10,
                11,
                12
              ]
            },
            "keyspace": "mybucket",
            "limit": "100",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"TYP\"",
                    "inclusion": 3,
                    "low": "\"TYP\""
                  },
                  {
                    "high": "\"FAILED\"",
                    "inclusion": 3,
                    "low": "\"FAILED\""
                  }
                ]
              }
            ],
            "using": "gsi"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "((cover ((`mybucket`.`type`)) = \"TYP\") and (cover ((`mybucket`.`status`)) = \"FAILED\"))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "cover ((`mybucket`.`id`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`status`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`subStatus`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`interaction`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`endTimestamp`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`failureReason`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`traceID`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`accountNumberToken`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`customerID`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`pcn`))"
                    },
                    {
                      "expr": "cover ((`mybucket`.`startTimestamp`))"
                    }
                  ]
                },
                {
                  "#operator": "FinalProject"
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "100"
      }
    ]
  },
  "text": "select id,status,subStatus,interaction,endTimestamp,failureReason,traceID,accountNumberToken,customerID,pcn,startTimestamp from mybucket where type=\"TYP\" and status = \"FAILED\" OFFSET 0 LIMIT 100;"
}
2

If there is ORDER BY and query is not following index order it must fetch all possible documents and do sort (last fetched entry might be first in the order).

The following article gives more details Using OFFSET and Keyset in N1QL | The Couchbase Blog

As no ORDER BY expression in query, it difficult provide index advise. You can use Index Advisor
https://index-advisor.couchbase.com/indexadvisor/#1

3

Thank you vsr1. This Index Advisor helped me lot. It has given me the below index for my sql which solved performance issue.

CREATE INDEX myindex ON mybucket(type,status,startTimestamp DESC,id,subStatus,interaction,endTimestamp,failureReason,traceID,accountNumberToken,customerID,pcn,name) WHERE ((type = “TYP”) and (status = “FAILED”)) WITH { “num_replica”:1 }