We’re having issues with a query not using any of our indexes. We have a users table, and each record has an array field called track_ids. We need to query all users who have track_ids (i.e. not a blank array).
The query we use is { track_ids: { $ne: [] } } which works fine. But it was performing a COLSCAN over 100,000 of users, taking up to 30s in the worst case. So we created an index on the track_ids column, but it is still doing a COLSCAN. Even when I use hint to specify the track_ids index, it uses the index, and then still proceeds to do a FETCH for the $ne condition.
So I looked into using partial indexes, and make the partial index condition the same as the query, but partial indexes do not support $ne conditions, so that approach didn’t pan out.
Other queries I’ve tried:
{ track_ids: { $not: { $size: 0 } } }
{ "track_ids.0": { $not: { $eq: null } } }
None off these use the index, and as a result, fallback to COLSCAN.
What is the best way to perform this type of query so that it uses an index?