N1ql does not use index for "order by"

SQL++
20

I’ve removed ‘form’ from the index.

This is my new index definition.

CREATE INDEX `idx_creatives_by_artist_test` ON `catalog`((-str_to_millis((`meta`.`updatedAt`))),`clientId`,`username`,(`values`.`deleted_at`),(`values`.`published`)) WHERE (`form` = "creative")

This is EXPLAIN out:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexScan",
              "covers": [
                "cover ((-str_to_millis(((`bucket1`.`meta`).`updatedAt`))))",
                "cover ((`bucket1`.`clientId`))",
                "cover ((`bucket1`.`username`))",
                "cover (((`bucket1`.`values`).`deleted_at`))",
                "cover (((`bucket1`.`values`).`published`))",
                "cover ((meta(`bucket1`).`id`))"
              ],
              "filter_covers": {
                "cover ((`bucket1`.`form`))": "creative"
              },
              "index": "idx_creatives_by_artist_test",
              "index_id": "8430fa9158884cc8",
              "keyspace": "catalog",
              "namespace": "default",
              "spans": [
                {
                  "Range": {
                    "Inclusion": 1,
                    "Low": [
                      "true",
                      "\"clientId\"",
                      "\"username\"",
                      "null",
                      "true"
                    ]
                  }
                }
              ],
              "using": "gsi"
            },
            {
              "#operator": "Parallel",
              "maxParallelism": 1,
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter",
                    "condition": "((((((cover ((`bucket1`.`clientId`)) = \"clientId\") and (cover ((`bucket1`.`form`)) = \"creative\")) and (cover ((`bucket1`.`username`)) = \"username\")) and (cover (((`bucket1`.`values`).`deleted_at`)) is null)) and (cover (((`bucket1`.`values`).`published`)) = true)) and cover ((-str_to_millis(((`bucket1`.`meta`).`updatedAt`)))))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "cover ((`bucket1`.`form`))"
                      }
                    ]
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Offset",
          "expr": "0"
        },
        {
          "#operator": "Limit",
          "expr": "30"
        },
        {
          "#operator": "FinalProject"
        }
      ]
    },
    "text": "select `bucket1`.`form` from catalog bucket1 \nwhere \nbucket1.clientId = 'clientId' \nand bucket1.form = 'creative' \nand bucket1.username = 'username' \nand `values`.deleted_at is null\nand `values`.published = true\nand -STR_TO_MILLIS(`meta`.`updatedAt`)\norder by -STR_TO_MILLIS(`meta`.`updatedAt`)\nlimit 30 offset 0"
  }
]

Query response time is between 900ms and 1.2 second.

Thank you for the help :slight_smile: