Query optimization

Working with Data
, ,
1

Hello All, I have problem with counts being slow (over 8 minutes). I finally figured out what the root problem is:

In our application, we use some field to mark it as system or as deleted.
When its not a systemdoc or deleted, the field is not present.

So in our count querys, we use this expression:
{ $or: [ { “systemdoc”: { $exists: false } }, { “systemdoc”: false } ] }

(Same goes for _deleted).

I have modified a test environment, to contain the systemdoc: false property and removed the $exists from the query. This brings the query down to a few miliseconds (becuase of an index).

Now we need to implement a fix for this (and _deleted, as its in every applicationrequest/missing in every document if not deleted).

The difficult way would be to add these fields to the documents, update our software to stop using the exists queries.

But Im wondering; Is there another way to solve this? (Partial indexes wont work, exists:false is not allowed).
Sparse indexes don’t seem to help. Anything I am missing?

2

Hi @Sander_de_Lange ,

You are not missing much. The way you fixed systemDoc is the way to go. It turns a non selective query into a selective one…

We generally recommend keeping counter values calculated as operations go into the database and each non deleted document is counted there as part of application flows.

Alternative could be calculating those counts using schedule materialised views with $merge.

Thanks
Pavel