What is the best way to optimise the following CosmosDb Query:
SELECT count(1)
FROM c
where c.creationDateTs > c.dueDateTs
AND c.partitionKey="DocReadTests_assignments"
At the moment is giving us a cost of 38210.17RUs on a container with just over 1 million records.
We have the standard oob container index policy (all fields are indexed) and also tried creating composite index on (creationDateTs ASC, dueDateTs ASC) and there was no change in consumption.
where c.creationDateTs > c.dueDateTs
Is not sargable.
The best that can be done is a seek on partitionKey and the remainder cannot be converted to a straight forward index seek and requires the pair of values from all documents in the logical partition to be retrieved and compared.
In theory this could be done by scanning all the entries in the composite index rather than retrieving the full documents but this is still less efficient than a seek - and sounds like this might not be implemented if you didn't see any change in RU after creating such an index.
Maybe you could also try a composite index of partitionKey ASC, creationDateTs ASC, dueDateTs ASC) to see if that fares any better (in allowing the exact index entries that match the logical partition key and so need to be scanned to be found).
If you add a new property to the document of DueDateCreationDateDiff that is just dueDateTs - creationDateTs (or based on DateTimeDiff depending on what format those properties are in) then you can do a straightforward index seek on
where c.DueDateCreationDateDiff < 0
AND c.partitionKey="DocReadTests_assignments"
Potentially the DueDateCreationDateDiff could be a computed property but this is still preview functionality at the moment so you may prefer to maintain it manually for now.