5

When executing the following query I receive an error

select * from c order by c.Agent.LastStateChangeUnixTime desc,c.Priority asc

The order by query does not have a corresponding composite index that it can be served from

I have added a composite index as stated in the documentation here and here

Is my composite index wrong? Or am I missing something else?

My index settings are:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/\"_etag\"/?"
        }
    ],
    "compositeIndexes": [
        [
            {
                "path": "/Priority",
                "order": "descending"
            },
            {
                "path": "/Agent/LastStateChangeUnixTime",
                "order": "ascending"
            }
        ]
    ]
}

Example Object:

{
    "Agent": {
        "TenantId": 999999,
        "PrimaryState": "Null",
        "PendingState": "Null",
        "LastStateChange": "2020-01-18T05:48:11.5397269+00:00",
        "LastStateChangeUnixTime": 1579326491,
        "Notes": null,
        "AgentId": 123,
        "id": "agent-123"
    },
    "AgentId": 123,
    "SkillName": "English",
    "id": "SkillName123",
    "Priority": 10,
    "_rid": "SVIMANKqJboCAAAAAAAAAA==",
    "_self": "dbs/SVIMAA==/colls/SVIMANKqJbo=/docs/SVIMANKqJboCAAAAAAAAAA==/",
    "_etag": "\"2200819a-0000-0700-0000-5e229c1d0000\"",
    "_attachments": "attachments/",
    "_ts": 1579326493
}
rbohac
  • 199
  • 1
  • 2
  • 6

1 Answers1

8

Yes, your composite index is incorrect for this query. The order in which they appear matters so WHERE clause needs to match the order.

Change or add the following to your composite index.

"compositeIndexes": [
    [
        {
            "path": "/Agent/LastStateChangeUnixTime",
            "order": "descending"
        },
        {
            "path": "/Priority",
            "order": "ascending"
        }
    ]
]
Mark Brown
  • 8,113
  • 2
  • 17
  • 21
  • Odd I tried switching the query and it still have the same error... `select * from c order by c.Agent.LastStateChangeUnixTime asc,c.Priority desc` – rbohac Jan 20 '20 at 16:40
  • 3
    Hi, I'm from the CosmosDB Engineering Team. We do not utilize the composite indexes until it gets fully built, so the error might continue for a while even after matching composite indexes are created. If you can monitor reindexer progress on your collection and re-try after the progress reaches 100%, then you shouldn't hit this error. We're working on improving the error message to indicate this particular case, to indicate that composite indexes cannot be used when it is being built, rather than indicating absence of composite indexes incorrectly. – Krishnan Sundaram Jan 20 '20 at 19:51
  • @KrishnanSundaram is correct here. Changing the index policy takes time as it must rebuild. Please let us know if the query works. Thanks. – Mark Brown Jan 21 '20 at 05:14
  • 1
    I still get the same error using mongo db api: The order by query does not have a corresponding composite index that it can be served from. – Fabian Börner Mar 07 '21 at 19:07