I have a problem with cosmos sql query, it's simple but also expensive:
SELECT * FROM c where c.key = '<key>' and c.lastAction > c.lastSync
With around 60k records in this key it's using crazy 1600 RUs while returning 0 records. I tried using composite indexes but they didn't help. What's strange when I do c.lastAction < c.lastSync it's returning a big 1000 records page with just of around 100 RUs.
The lastAction and lastSync are just a date types, ex: '2021-12-07T08:20:00.877192Z', '2021-12-07T07:40:35Z' (I know this model should be designed differently but this is where we are at the moment) I'm also thinking about adding property computed during upsert that will return result of c.lastAction > c.lastSync if nothing else will work.
Edit It looks like it's doing a full read of records to read this c.lastSync, if so is there a way to index/workaround it?