Why is $facet so Slow?

[steevej (Steeve Juneau)]

I really do not know why the explain plan seems to indicate (for count_scan) 0ms (number on the left) while Compass indicates 11ms.

The issue is that without the $facet the FETCH only process 10 documents while all documents are FETCHed for $facet.

Try to move $sort right after the $match.

One hack you could try to use $unionWith something like:

[
 { $match ... } ,
 { $count ... } ,
 { $unionWith : {
    coll: "same collection" ,
    pipeline : [
         { $match ... } ,
         { $sort ... } ,
         { $skip ... } ,
         { $limit ... } ,
         { $project ... }
    ]
 } }
]

This might avoid FETCH of all documents.