[SERVER-13754] Bounds should not be combined for $or queries that can use merge sort Created: 27/Apr/14  Updated: 11/Jul/16  Resolved: 09/May/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.0
Fix Version/s: 2.6.2, 2.7.1

Type: Bug Priority: Major - P3
Reporter: Edward Assignee: David Storch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Operating System: ALL
Backport Completed:
Participants:

 Description   
Issue Status as of May 15, 2014

ISSUE SUMMARY
Sometimes, sorted $or queries that require multiple index scans over the same index incorrectly perform an in-memory sort, instead of a merge sort. An in-memory sort has higher memory requirements than merge sort, and may require scanning more documents or index keys than a merge sort. This means that the query planner should prefer merge sort solutions to in-memory sort solutions where possible.

USER IMPACT
Users may experience low performance on these type of queries.

WORKAROUNDS
There are no known workarounds. Users experiencing severe performance degradation can consider downgrading to the 2.4.10 production release until a fix is released.

AFFECTED VERSIONS
Production versions 2.6.0 and 2.6.1 are affected by this issue.

FIX VERSION
The fix is included in the 2.6.2 production release.

RESOLUTION DETAILS
The QueryPlannerAnalysis phase joins the results of multiple index scans for affected $or queries with a mergeSort when the sort order is shared.

Original description.

It seems that at current version (2.6.1), the query optimizer is still unable to optimize queries that use $or, $in, limit() and sort() all at once. The https://jira.mongodb.org/browse/SERVER-1205 and https://jira.mongodb.org/browse/SERVER-3310 fixes, each only improved performance on queries having 3 out of the 4 operations listed above. When introducing a 4th operation into the query, the optimization goes out the window. This behavior is observed with full index and document scans within the $or clause, even though a limit() is specified.

Suppose I have an application that needs to find the 10 latest newsfeeds that are either:
a) Posted by me,
b) Posted by my friends that have permission types of 'public', or 'friend',
c) Designated to a specified group of users which included me.

Suppose the indexes on this collection are:

{owner:1, create_time: -1, permission: 1}

Compound Index

{designate:1, create_time: -1}

Compound Multikey Index where 'designate' is an array.

The following query is not optimized, and scans way more documents then it should:

db.stream.find(
    {$or: [
        {owner: my_id },
        {owner: {$in: [lots_of_friend_ids]}, permission: {$in: ['public', 'friend']}},
        {designate: my_id}
    ]}
).limit(10).sort({create_time: -1})



 Comments   
Comment by Armano [ 26/Jan/15 ]

ok

Comment by Ramon Fernandez Marina [ 26/Jan/15 ]

armano, can you please open a separate ticket for the $and/$all operands? Can you also include the output of the same command using explain?

Thanks,
Ramón.

Comment by Armano [ 26/Jan/15 ]

This issue is still in 2.6.7 mongodb version, but is affected to $and, $all operand.
This query with same indexes between 2.4.x and 2.6.7 is 100% slower.

db.foo.aggregate([
  {
      $match:
      {
          "query_field":
          {
              $all: [1, 57, 13, 55, 139]
          }
      }
  },
  {
      $sort:
      {
          "sort_field": -1
      }
  },
  {
      $limit: 50
  }
])

Comment by Githook User [ 16/May/14 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-13754 explode OR solutions for sort
(cherry picked from commit 4fd34d5272e6b282f5b98b7b65c4ba2c092d93fb)
Branch: v2.6
https://github.com/mongodb/mongo/commit/4649c7185dd3b7f650eb596646d4164d496e6d43

Comment by Githook User [ 09/May/14 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-13754 explode OR solutions for sort
Branch: master
https://github.com/mongodb/mongo/commit/4fd34d5272e6b282f5b98b7b65c4ba2c092d93fb

Comment by J Rassi [ 28/Apr/14 ]

Thanks for reporting this issue. I have identified the underlying problem as follows: sometimes, sorted $or queries that require multiple index scans over the same index do not qualify for a merge sort when they should be able to, and instead are assigned a query plan that performs an in-memory sort. When a query requires an in-memory sort on a field, index scans on that field do not use a limit (because the sort is to reveal where in the index range the "top K" values live); this is why your query is scanning a large number of documents.

The following illustrates two sorted $or queries that require multiple index scans over the same index, one of which qualifies for a merge sort and one of which (incorrectly) does not.

db.foo.ensureIndex({a:1, c:1})
db.foo.ensureIndex({b:1, c:1})
db.foo.find({$or:[{a:1}, {b:1}, {b:2, x:1}]}).sort({c:1}).explain(true) // correct: uses SORT_MERGE stage
db.foo.find({$or:[{a:1}, {b:1}, {b:2}]}).sort({c:1}).explain(true) // incorrect: uses SORT stage

For your query, your workaround of expanding the $in clause into separate clauses is the correct method of avoiding this problem.

Please continue to watch this ticket for updates.

Comment by Edward [ 28/Apr/14 ]

Okay, this is going to be somewhat long. I am going to provide 3 explain() outputs.
Testing data is made up from total of 60 records in the collection. The 'lots_of_friend_ids' in this case is 8 distinct ids.

First one is the explain() output from the query stated in the ticket:

db.stream.find(
    {$or: [
        {owner: my_id },
        {owner: {$in: [lots_of_friend_ids]}, permission: {$in: ['public', 'friend']}},
        {designate: my_id}
    ]}
).limit(10).sort({create_time: -1})

2nd one is the same query without the sort():

db.stream.find(
    {$or: [
        {owner: my_id },
        {owner: {$in: [lots_of_friend_ids]}, permission: {$in: ['public', 'friend']}},
        {designate: my_id}
    ]}
).limit(10)

3rd one is my current work around by 'flattening' owner: $in: lots_of_friend_ids into multiple top level $or clauses:

db.stream.find(
    {$or: [
        {owner: my_id },
        {owner: '1st_id', permission: {$in: ['public', 'friend']}},
        {owner: '2nd_id', permission: {$in: ['public', 'friend']}},
        {owner: '3rd_id', permission: {$in: ['public', 'friend']}},
        {...},
        {designate: my_id}
    ]}
).limit(10).sort({create_time: -1})

So, the first explain() output:

{
    "clauses": [
        {
            "clauses": [
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 29,
                    "nscannedObjects": 29,
                    "nscanned": 32,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd476347b1b8",
                                "52ddd476347b1b8"
                            ],
                            [
                                "52ddd4783b635c3",
                                "52ddd4783b635c3"
                            ],
                            [
                                "52ddd479eab21d8",
                                "52ddd479eab21d8"
                            ],
                            [
                                "531e84d1713f6be",
                                "531e84d1713f6be"
                            ],
                            [
                                "531e84d39af5400",
                                "531e84d39af5400"
                            ],
                            [
                                "531e84d58833772",
                                "531e84d58833772"
                            ],
                            [
                                "531e85792aaefa3",
                                "531e85792aaefa3"
                            ],
                            [
                                "531e94d8e0c3a62",
                                "531e94d8e0c3a62"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 29,
                    "nscannedObjects": 29,
                    "nscanned": 29,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd47ba6c59d4",
                                "52ddd47ba6c59d4"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                {
                                    "$minElement": 1
                                },
                                {
                                    "$maxElement": 1
                                }
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor designate_1_ca_-1",
                    "isMultiKey": true,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 1,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "designate": [
                            [
                                "52ddd47ba6c59d4",
                                "52ddd47ba6c59d4"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ]
                    }
                }
            ],
            "cursor": "QueryOptimizerCursor",
            "n": 10,
            "nscannedObjects": 59,
            "nscanned": 62,
            "scanAndOrder": true,
            "nChunkSkips": 0
        },
        {
            "clauses": [
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 29,
                    "nscannedObjects": 29,
                    "nscanned": 32,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd476347b1b8",
                                "52ddd476347b1b8"
                            ],
                            [
                                "52ddd4783b635c3",
                                "52ddd4783b635c3"
                            ],
                            [
                                "52ddd479eab21d8",
                                "52ddd479eab21d8"
                            ],
                            [
                                "531e84d1713f6be",
                                "531e84d1713f6be"
                            ],
                            [
                                "531e84d39af5400",
                                "531e84d39af5400"
                            ],
                            [
                                "531e84d58833772",
                                "531e84d58833772"
                            ],
                            [
                                "531e85792aaefa3",
                                "531e85792aaefa3"
                            ],
                            [
                                "531e94d8e0c3a62",
                                "531e94d8e0c3a62"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 29,
                    "nscannedObjects": 29,
                    "nscanned": 29,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd47ba6c59d4",
                                "52ddd47ba6c59d4"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                {
                                    "$minElement": 1
                                },
                                {
                                    "$maxElement": 1
                                }
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor designate_1_ca_-1",
                    "isMultiKey": true,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 1,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "designate": [
                            [
                                "52ddd47ba6c59d4",
                                "52ddd47ba6c59d4"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ]
                    }
                }
            ],
            "cursor": "QueryOptimizerCursor",
            "n": 59,
            "nscannedObjects": 59,
            "nscanned": 62,
            "scanAndOrder": true,
            "nChunkSkips": 0
        }
    ],
    "cursor": "QueryOptimizerCursor",
    "n": 10,
    "nscannedObjects": 118,
    "nscanned": 124,
    "nscannedObjectsAllPlans": 118,
    "nscannedAllPlans": 124,
    "scanAndOrder": false,
    "nYields": 1,
    "nChunkSkips": 0,
    "millis": 2,
    "allPlans": [
        {
            "clauses": [
                {
                    "clauses": [
                        {
                            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                            "isMultiKey": false,
                            "n": 29,
                            "nscannedObjects": 29,
                            "nscanned": 32,
                            "scanAndOrder": false,
                            "indexOnly": false,
                            "nChunkSkips": 0,
                            "indexBounds": {
                                "owner": [
                                    [
                                        "52ddd476347b1b8",
                                        "52ddd476347b1b8"
                                    ],
                                    [
                                        "52ddd4783b635c3",
                                        "52ddd4783b635c3"
                                    ],
                                    [
                                        "52ddd479eab21d8",
                                        "52ddd479eab21d8"
                                    ],
                                    [
                                        "531e84d1713f6be",
                                        "531e84d1713f6be"
                                    ],
                                    [
                                        "531e84d39af5400",
                                        "531e84d39af5400"
                                    ],
                                    [
                                        "531e84d58833772",
                                        "531e84d58833772"
                                    ],
                                    [
                                        "531e85792aaefa3",
                                        "531e85792aaefa3"
                                    ],
                                    [
                                        "531e94d8e0c3a62",
                                        "531e94d8e0c3a62"
                                    ]
                                ],
                                "ca": [
                                    [
                                        {
                                            "$maxElement": 1
                                        },
                                        {
                                            "$minElement": 1
                                        }
                                    ]
                                ],
                                "permission": [
                                    [
                                        "F",
                                        "F"
                                    ],
                                    [
                                        "P",
                                        "P"
                                    ]
                                ]
                            }
                        },
                        {
                            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                            "isMultiKey": false,
                            "n": 29,
                            "nscannedObjects": 29,
                            "nscanned": 29,
                            "scanAndOrder": false,
                            "indexOnly": false,
                            "nChunkSkips": 0,
                            "indexBounds": {
                                "owner": [
                                    [
                                        "52ddd47ba6c59d4",
                                        "52ddd47ba6c59d4"
                                    ]
                                ],
                                "ca": [
                                    [
                                        {
                                            "$maxElement": 1
                                        },
                                        {
                                            "$minElement": 1
                                        }
                                    ]
                                ],
                                "permission": [
                                    [
                                        {
                                            "$minElement": 1
                                        },
                                        {
                                            "$maxElement": 1
                                        }
                                    ]
                                ]
                            }
                        },
                        {
                            "cursor": "BtreeCursor designate_1_ca_-1",
                            "isMultiKey": true,
                            "n": 1,
                            "nscannedObjects": 1,
                            "nscanned": 1,
                            "scanAndOrder": false,
                            "indexOnly": false,
                            "nChunkSkips": 0,
                            "indexBounds": {
                                "designate": [
                                    [
                                        "52ddd47ba6c59d4",
                                        "52ddd47ba6c59d4"
                                    ]
                                ],
                                "ca": [
                                    [
                                        {
                                            "$maxElement": 1
                                        },
                                        {
                                            "$minElement": 1
                                        }
                                    ]
                                ]
                            }
                        }
                    ],
                    "cursor": "QueryOptimizerCursor",
                    "n": 10,
                    "nscannedObjects": 59,
                    "nscanned": 62,
                    "scanAndOrder": true,
                    "nChunkSkips": 0
                },
                {
                    "clauses": [
                        {
                            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                            "isMultiKey": false,
                            "n": 29,
                            "nscannedObjects": 29,
                            "nscanned": 32,
                            "scanAndOrder": false,
                            "indexOnly": false,
                            "nChunkSkips": 0,
                            "indexBounds": {
                                "owner": [
                                    [
                                        "52ddd476347b1b8",
                                        "52ddd476347b1b8"
                                    ],
                                    [
                                        "52ddd4783b635c3",
                                        "52ddd4783b635c3"
                                    ],
                                    [
                                        "52ddd479eab21d8",
                                        "52ddd479eab21d8"
                                    ],
                                    [
                                        "531e84d1713f6be",
                                        "531e84d1713f6be"
                                    ],
                                    [
                                        "531e84d39af5400",
                                        "531e84d39af5400"
                                    ],
                                    [
                                        "531e84d58833772",
                                        "531e84d58833772"
                                    ],
                                    [
                                        "531e85792aaefa3",
                                        "531e85792aaefa3"
                                    ],
                                    [
                                        "531e94d8e0c3a62",
                                        "531e94d8e0c3a62"
                                    ]
                                ],
                                "ca": [
                                    [
                                        {
                                            "$maxElement": 1
                                        },
                                        {
                                            "$minElement": 1
                                        }
                                    ]
                                ],
                                "permission": [
                                    [
                                        "F",
                                        "F"
                                    ],
                                    [
                                        "P",
                                        "P"
                                    ]
                                ]
                            }
                        },
                        {
                            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                            "isMultiKey": false,
                            "n": 29,
                            "nscannedObjects": 29,
                            "nscanned": 29,
                            "scanAndOrder": false,
                            "indexOnly": false,
                            "nChunkSkips": 0,
                            "indexBounds": {
                                "owner": [
                                    [
                                        "52ddd47ba6c59d4",
                                        "52ddd47ba6c59d4"
                                    ]
                                ],
                                "ca": [
                                    [
                                        {
                                            "$maxElement": 1
                                        },
                                        {
                                            "$minElement": 1
                                        }
                                    ]
                                ],
                                "permission": [
                                    [
                                        {
                                            "$minElement": 1
                                        },
                                        {
                                            "$maxElement": 1
                                        }
                                    ]
                                ]
                            }
                        },
                        {
                            "cursor": "BtreeCursor designate_1_ca_-1",
                            "isMultiKey": true,
                            "n": 1,
                            "nscannedObjects": 1,
                            "nscanned": 1,
                            "scanAndOrder": false,
                            "indexOnly": false,
                            "nChunkSkips": 0,
                            "indexBounds": {
                                "designate": [
                                    [
                                        "52ddd47ba6c59d4",
                                        "52ddd47ba6c59d4"
                                    ]
                                ],
                                "ca": [
                                    [
                                        {
                                            "$maxElement": 1
                                        },
                                        {
                                            "$minElement": 1
                                        }
                                    ]
                                ]
                            }
                        }
                    ],
                    "cursor": "QueryOptimizerCursor",
                    "n": 59,
                    "nscannedObjects": 59,
                    "nscanned": 62,
                    "scanAndOrder": true,
                    "nChunkSkips": 0
                }
            ],
            "cursor": "QueryOptimizerCursor",
            "n": 59,
            "nscannedObjects": 118,
            "nscanned": 124,
            "scanAndOrder": false,
            "nChunkSkips": 0
        }
    ],
    "server": "Xres:27017",
    "filterSet": false,
    "stats": {
        "type": "KEEP_MUTATIONS",
        "works": 197,
        "yields": 1,
        "unyields": 1,
        "invalidates": 0,
        "advanced": 59,
        "needTime": 137,
        "needFetch": 0,
        "isEOF": 1,
        "children": [
            {
                "type": "OR",
                "works": 197,
                "yields": 1,
                "unyields": 1,
                "invalidates": 0,
                "advanced": 59,
                "needTime": 137,
                "needFetch": 0,
                "isEOF": 1,
                "dupsTested": 69,
                "dupsDropped": 10,
                "locsForgotten": 0,
                "matchTested_0": 0,
                "matchTested_1": 0,
                "children": [
                    {
                        "type": "SORT",
                        "works": 74,
                        "yields": 1,
                        "unyields": 1,
                        "invalidates": 0,
                        "advanced": 10,
                        "needTime": 62,
                        "needFetch": 0,
                        "isEOF": 1,
                        "forcedFetches": 0,
                        "memUsage": 4331,
                        "memLimit": 33554432,
                        "children": [
                            {
                                "type": "FETCH",
                                "works": 62,
                                "yields": 1,
                                "unyields": 1,
                                "invalidates": 0,
                                "advanced": 59,
                                "needTime": 2,
                                "needFetch": 0,
                                "isEOF": 1,
                                "alreadyHasObj": 0,
                                "forcedFetches": 0,
                                "matchTested": 0,
                                "children": [
                                    {
                                        "type": "OR",
                                        "works": 62,
                                        "yields": 1,
                                        "unyields": 1,
                                        "invalidates": 0,
                                        "advanced": 59,
                                        "needTime": 2,
                                        "needFetch": 0,
                                        "isEOF": 1,
                                        "dupsTested": 59,
                                        "dupsDropped": 0,
                                        "locsForgotten": 0,
                                        "matchTested_0": 0,
                                        "matchTested_1": 0,
                                        "matchTested_2": 0,
                                        "children": [
                                            {
                                                "type": "IXSCAN",
                                                "works": 30,
                                                "yields": 1,
                                                "unyields": 1,
                                                "invalidates": 0,
                                                "advanced": 29,
                                                "needTime": 0,
                                                "needFetch": 0,
                                                "isEOF": 1,
                                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                                "boundsVerbose": "field #0['owner']: [\"52ddd476347b1b8\", \"52ddd476347b1b8\"], [\"52ddd4783b635c3\", \"52ddd4783b635c3\"], [\"52ddd479eab21d8\", \"52ddd479eab21d8\"], [\"531e84d1713f6be\", \"531e84d1713f6be\"], [\"531e84d39af5400\", \"531e84d39af5400\"], [\"531e84d58833772\", \"531e84d58833772\"], [\"531e85792aaefa3\", \"531e85792aaefa3\"], [\"531e94d8e0c3a62\", \"531e94d8e0c3a62\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                                "isMultiKey": 0,
                                                "yieldMovedCursor": 0,
                                                "dupsTested": 0,
                                                "dupsDropped": 0,
                                                "seenInvalidated": 0,
                                                "matchTested": 0,
                                                "keysExamined": 32,
                                                "children": [
 
                                                ]
                                            },
                                            {
                                                "type": "IXSCAN",
                                                "works": 30,
                                                "yields": 1,
                                                "unyields": 1,
                                                "invalidates": 0,
                                                "advanced": 29,
                                                "needTime": 0,
                                                "needFetch": 0,
                                                "isEOF": 1,
                                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                                "boundsVerbose": "field #0['owner']: [\"52ddd47ba6c59d4\", \"52ddd47ba6c59d4\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [MinKey, MaxKey]",
                                                "isMultiKey": 0,
                                                "yieldMovedCursor": 0,
                                                "dupsTested": 0,
                                                "dupsDropped": 0,
                                                "seenInvalidated": 0,
                                                "matchTested": 0,
                                                "keysExamined": 29,
                                                "children": [
 
                                                ]
                                            },
                                            {
                                                "type": "IXSCAN",
                                                "works": 2,
                                                "yields": 1,
                                                "unyields": 1,
                                                "invalidates": 0,
                                                "advanced": 1,
                                                "needTime": 0,
                                                "needFetch": 0,
                                                "isEOF": 1,
                                                "keyPattern": "{ designate: 1, ca: -1 }",
                                                "boundsVerbose": "field #0['designate']: [\"52ddd47ba6c59d4\", \"52ddd47ba6c59d4\"], field #1['ca']: [MaxKey, MinKey]",
                                                "isMultiKey": 1,
                                                "yieldMovedCursor": 0,
                                                "dupsTested": 1,
                                                "dupsDropped": 0,
                                                "seenInvalidated": 0,
                                                "matchTested": 0,
                                                "keysExamined": 1,
                                                "children": [
 
                                                ]
                                            }
                                        ]
                                    }
                                ]
                            }
                        ]
                    },
                    {
                        "type": "SORT",
                        "works": 123,
                        "yields": 1,
                        "unyields": 1,
                        "invalidates": 0,
                        "advanced": 59,
                        "needTime": 62,
                        "needFetch": 0,
                        "isEOF": 1,
                        "forcedFetches": 0,
                        "memUsage": 29388,
                        "memLimit": 33554432,
                        "children": [
                            {
                                "type": "FETCH",
                                "works": 62,
                                "yields": 1,
                                "unyields": 1,
                                "invalidates": 0,
                                "advanced": 59,
                                "needTime": 2,
                                "needFetch": 0,
                                "isEOF": 1,
                                "alreadyHasObj": 0,
                                "forcedFetches": 0,
                                "matchTested": 0,
                                "children": [
                                    {
                                        "type": "OR",
                                        "works": 62,
                                        "yields": 1,
                                        "unyields": 1,
                                        "invalidates": 0,
                                        "advanced": 59,
                                        "needTime": 2,
                                        "needFetch": 0,
                                        "isEOF": 1,
                                        "dupsTested": 59,
                                        "dupsDropped": 0,
                                        "locsForgotten": 0,
                                        "matchTested_0": 0,
                                        "matchTested_1": 0,
                                        "matchTested_2": 0,
                                        "children": [
                                            {
                                                "type": "IXSCAN",
                                                "works": 30,
                                                "yields": 1,
                                                "unyields": 1,
                                                "invalidates": 0,
                                                "advanced": 29,
                                                "needTime": 0,
                                                "needFetch": 0,
                                                "isEOF": 1,
                                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                                "boundsVerbose": "field #0['owner']: [\"52ddd476347b1b8\", \"52ddd476347b1b8\"], [\"52ddd4783b635c3\", \"52ddd4783b635c3\"], [\"52ddd479eab21d8\", \"52ddd479eab21d8\"], [\"531e84d1713f6be\", \"531e84d1713f6be\"], [\"531e84d39af5400\", \"531e84d39af5400\"], [\"531e84d58833772\", \"531e84d58833772\"], [\"531e85792aaefa3\", \"531e85792aaefa3\"], [\"531e94d8e0c3a62\", \"531e94d8e0c3a62\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                                "isMultiKey": 0,
                                                "yieldMovedCursor": 0,
                                                "dupsTested": 0,
                                                "dupsDropped": 0,
                                                "seenInvalidated": 0,
                                                "matchTested": 0,
                                                "keysExamined": 32,
                                                "children": [
 
                                                ]
                                            },
                                            {
                                                "type": "IXSCAN",
                                                "works": 30,
                                                "yields": 1,
                                                "unyields": 1,
                                                "invalidates": 0,
                                                "advanced": 29,
                                                "needTime": 0,
                                                "needFetch": 0,
                                                "isEOF": 1,
                                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                                "boundsVerbose": "field #0['owner']: [\"52ddd47ba6c59d4\", \"52ddd47ba6c59d4\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [MinKey, MaxKey]",
                                                "isMultiKey": 0,
                                                "yieldMovedCursor": 0,
                                                "dupsTested": 0,
                                                "dupsDropped": 0,
                                                "seenInvalidated": 0,
                                                "matchTested": 0,
                                                "keysExamined": 29,
                                                "children": [
 
                                                ]
                                            },
                                            {
                                                "type": "IXSCAN",
                                                "works": 2,
                                                "yields": 1,
                                                "unyields": 1,
                                                "invalidates": 0,
                                                "advanced": 1,
                                                "needTime": 0,
                                                "needFetch": 0,
                                                "isEOF": 1,
                                                "keyPattern": "{ designate: 1, ca: -1 }",
                                                "boundsVerbose": "field #0['designate']: [\"52ddd47ba6c59d4\", \"52ddd47ba6c59d4\"], field #1['ca']: [MaxKey, MinKey]",
                                                "isMultiKey": 1,
                                                "yieldMovedCursor": 0,
                                                "dupsTested": 1,
                                                "dupsDropped": 0,
                                                "seenInvalidated": 0,
                                                "matchTested": 0,
                                                "keysExamined": 1,
                                                "children": [
 
                                                ]
                                            }
                                        ]
                                    }
                                ]
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

2nd explain() output:

{
    "clauses": [
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 10,
            "nscannedObjects": 10,
            "nscanned": 12,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "52ddd476347b1b8",
                        "52ddd476347b1b8"
                    ],
                    [
                        "52ddd4783b635c3",
                        "52ddd4783b635c3"
                    ],
                    [
                        "52ddd479eab21d8",
                        "52ddd479eab21d8"
                    ],
                    [
                        "531e84d1713f6be",
                        "531e84d1713f6be"
                    ],
                    [
                        "531e84d39af5400",
                        "531e84d39af5400"
                    ],
                    [
                        "531e84d58833772",
                        "531e84d58833772"
                    ],
                    [
                        "531e85792aaefa3",
                        "531e85792aaefa3"
                    ],
                    [
                        "531e94d8e0c3a62",
                        "531e94d8e0c3a62"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor ",
            "isMultiKey": false,
            "n": 0,
            "nscannedObjects": 0,
            "nscanned": 0,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "52ddd47ba6c59d4",
                        "52ddd47ba6c59d4"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        {
                            "$minElement": 1
                        },
                        {
                            "$maxElement": 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor ",
            "isMultiKey": false,
            "n": 0,
            "nscannedObjects": 0,
            "nscanned": 0,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "designate": [
                    [
                        "52ddd47ba6c59d4",
                        "52ddd47ba6c59d4"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ]
            }
        }
    ],
    "cursor": "QueryOptimizerCursor",
    "n": 10,
    "nscannedObjects": 10,
    "nscanned": 12,
    "nscannedObjectsAllPlans": 10,
    "nscannedAllPlans": 12,
    "scanAndOrder": false,
    "nYields": 0,
    "nChunkSkips": 0,
    "millis": 0,
    "allPlans": [
        {
            "clauses": [
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 10,
                    "nscannedObjects": 10,
                    "nscanned": 12,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd476347b1b8",
                                "52ddd476347b1b8"
                            ],
                            [
                                "52ddd4783b635c3",
                                "52ddd4783b635c3"
                            ],
                            [
                                "52ddd479eab21d8",
                                "52ddd479eab21d8"
                            ],
                            [
                                "531e84d1713f6be",
                                "531e84d1713f6be"
                            ],
                            [
                                "531e84d39af5400",
                                "531e84d39af5400"
                            ],
                            [
                                "531e84d58833772",
                                "531e84d58833772"
                            ],
                            [
                                "531e85792aaefa3",
                                "531e85792aaefa3"
                            ],
                            [
                                "531e94d8e0c3a62",
                                "531e94d8e0c3a62"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor ",
                    "isMultiKey": false,
                    "n": 0,
                    "nscannedObjects": 0,
                    "nscanned": 0,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd47ba6c59d4",
                                "52ddd47ba6c59d4"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                {
                                    "$minElement": 1
                                },
                                {
                                    "$maxElement": 1
                                }
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor ",
                    "isMultiKey": false,
                    "n": 0,
                    "nscannedObjects": 0,
                    "nscanned": 0,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "designate": [
                            [
                                "52ddd47ba6c59d4",
                                "52ddd47ba6c59d4"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ]
                    }
                }
            ],
            "cursor": "QueryOptimizerCursor",
            "n": 10,
            "nscannedObjects": 10,
            "nscanned": 12,
            "scanAndOrder": false,
            "nChunkSkips": 0
        }
    ],
    "server": "Xres:27017",
    "filterSet": false,
    "stats": {
        "type": "LIMIT",
        "works": 11,
        "yields": 0,
        "unyields": 0,
        "invalidates": 0,
        "advanced": 10,
        "needTime": 0,
        "needFetch": 0,
        "isEOF": 1,
        "children": [
            {
                "type": "FETCH",
                "works": 10,
                "yields": 0,
                "unyields": 0,
                "invalidates": 0,
                "advanced": 10,
                "needTime": 0,
                "needFetch": 0,
                "isEOF": 0,
                "alreadyHasObj": 0,
                "forcedFetches": 0,
                "matchTested": 0,
                "children": [
                    {
                        "type": "OR",
                        "works": 10,
                        "yields": 0,
                        "unyields": 0,
                        "invalidates": 0,
                        "advanced": 10,
                        "needTime": 0,
                        "needFetch": 0,
                        "isEOF": 0,
                        "dupsTested": 10,
                        "dupsDropped": 0,
                        "locsForgotten": 0,
                        "matchTested_0": 0,
                        "matchTested_1": 0,
                        "matchTested_2": 0,
                        "children": [
                            {
                                "type": "IXSCAN",
                                "works": 10,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 10,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"52ddd476347b1b8\", \"52ddd476347b1b8\"], [\"52ddd4783b635c3\", \"52ddd4783b635c3\"], [\"52ddd479eab21d8\", \"52ddd479eab21d8\"], [\"531e84d1713f6be\", \"531e84d1713f6be\"], [\"531e84d39af5400\", \"531e84d39af5400\"], [\"531e84d58833772\", \"531e84d58833772\"], [\"531e85792aaefa3\", \"531e85792aaefa3\"], [\"531e94d8e0c3a62\", \"531e94d8e0c3a62\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 12,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 0,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 0,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{}",
                                "boundsVerbose": "field #0['owner']: [\"52ddd47ba6c59d4\", \"52ddd47ba6c59d4\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [MinKey, MaxKey]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 0,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 0,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 0,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{}",
                                "boundsVerbose": "field #0['designate']: [\"52ddd47ba6c59d4\", \"52ddd47ba6c59d4\"], field #1['ca']: [MaxKey, MinKey]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 0,
                                "children": [
 
                                ]
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

3rd explain() output (current work around):

{
    "clauses": [
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 1,
            "nscannedObjects": 1,
            "nscanned": 2,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "52ddd476347b1b8",
                        "52ddd476347b1b8"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 1,
            "nscannedObjects": 1,
            "nscanned": 2,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "52ddd479eab21d8",
                        "52ddd479eab21d8"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 2,
            "nscannedObjects": 2,
            "nscanned": 3,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "52ddd476347b1b8",
                        "52ddd476347b1b8"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 1,
            "nscannedObjects": 1,
            "nscanned": 2,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "52ddd4783b635c3",
                        "52ddd4783b635c3"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 2,
            "nscannedObjects": 2,
            "nscanned": 3,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "52ddd479eab21d8",
                        "52ddd479eab21d8"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 3,
            "nscannedObjects": 3,
            "nscanned": 3,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "531e84d1713f6be",
                        "531e84d1713f6be"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 1,
            "nscannedObjects": 1,
            "nscanned": 1,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "531e84d39af5400",
                        "531e84d39af5400"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 1,
            "nscannedObjects": 1,
            "nscanned": 1,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "531e84d58833772",
                        "531e84d58833772"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 1,
            "nscannedObjects": 1,
            "nscanned": 1,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "531e85792aaefa3",
                        "531e85792aaefa3"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 2,
            "nscannedObjects": 2,
            "nscanned": 3,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "531e94d8e0c3a62",
                        "531e94d8e0c3a62"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        "F",
                        "F"
                    ],
                    [
                        "P",
                        "P"
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
            "isMultiKey": false,
            "n": 6,
            "nscannedObjects": 6,
            "nscanned": 7,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "owner": [
                    [
                        "52ddd47ba6c59d4",
                        "52ddd47ba6c59d4"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ],
                "permission": [
                    [
                        {
                            "$minElement": 1
                        },
                        {
                            "$maxElement": 1
                        }
                    ]
                ]
            }
        },
        {
            "cursor": "BtreeCursor designate_1_ca_-1",
            "isMultiKey": true,
            "n": 1,
            "nscannedObjects": 1,
            "nscanned": 1,
            "scanAndOrder": false,
            "indexOnly": false,
            "nChunkSkips": 0,
            "indexBounds": {
                "designate": [
                    [
                        "52ddd47ba6c59d4",
                        "52ddd47ba6c59d4"
                    ]
                ],
                "ca": [
                    [
                        {
                            "$maxElement": 1
                        },
                        {
                            "$minElement": 1
                        }
                    ]
                ]
            }
        }
    ],
    "cursor": "QueryOptimizerCursor",
    "n": 10,
    "nscannedObjects": 22,
    "nscanned": 29,
    "nscannedObjectsAllPlans": 22,
    "nscannedAllPlans": 29,
    "scanAndOrder": false,
    "nYields": 0,
    "nChunkSkips": 0,
    "millis": 2,
    "allPlans": [
        {
            "clauses": [
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 2,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd476347b1b8",
                                "52ddd476347b1b8"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 2,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd479eab21d8",
                                "52ddd479eab21d8"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 2,
                    "nscannedObjects": 2,
                    "nscanned": 3,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd476347b1b8",
                                "52ddd476347b1b8"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
 
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 2,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd4783b635c3",
                                "52ddd4783b635c3"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 2,
                    "nscannedObjects": 2,
                    "nscanned": 3,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd479eab21d8",
                                "52ddd479eab21d8"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 3,
                    "nscannedObjects": 3,
                    "nscanned": 3,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "531e84d1713f6be",
                                "531e84d1713f6be"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 1,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "531e84d39af5400",
                                "531e84d39af5400"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 1,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "531e84d58833772",
                                "531e84d58833772"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 1,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "531e85792aaefa3",
                                "531e85792aaefa3"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 2,
                    "nscannedObjects": 2,
                    "nscanned": 3,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "531e94d8e0c3a62",
                                "531e94d8e0c3a62"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                "F",
                                "F"
                            ],
                            [
                                "P",
                                "P"
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor owner_1_ca_-1_permission_1",
                    "isMultiKey": false,
                    "n": 6,
                    "nscannedObjects": 6,
                    "nscanned": 7,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "owner": [
                            [
                                "52ddd47ba6c59d4",
                                "52ddd47ba6c59d4"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ],
                        "permission": [
                            [
                                {
                                    "$minElement": 1
                                },
                                {
                                    "$maxElement": 1
                                }
                            ]
                        ]
                    }
                },
                {
                    "cursor": "BtreeCursor designate_1_ca_-1",
                    "isMultiKey": true,
                    "n": 1,
                    "nscannedObjects": 1,
                    "nscanned": 1,
                    "scanAndOrder": false,
                    "indexOnly": false,
                    "nChunkSkips": 0,
                    "indexBounds": {
                        "designate": [
                            [
                                "52ddd47ba6c59d4",
                                "52ddd47ba6c59d4"
                            ]
                        ],
                        "ca": [
                            [
                                {
                                    "$maxElement": 1
                                },
                                {
                                    "$minElement": 1
                                }
                            ]
                        ]
                    }
                }
            ],
            "cursor": "QueryOptimizerCursor",
            "n": 10,
            "nscannedObjects": 22,
            "nscanned": 29,
            "scanAndOrder": false,
            "nChunkSkips": 0
        }
    ],
    "server": "Xres:27017",
    "filterSet": false,
    "stats": {
        "type": "LIMIT",
        "works": 34,
        "yields": 0,
        "unyields": 0,
        "invalidates": 0,
        "advanced": 10,
        "needTime": 23,
        "needFetch": 0,
        "isEOF": 1,
        "children": [
            {
                "type": "FETCH",
                "works": 33,
                "yields": 0,
                "unyields": 0,
                "invalidates": 0,
                "advanced": 10,
                "needTime": 23,
                "needFetch": 0,
                "isEOF": 0,
                "alreadyHasObj": 0,
                "forcedFetches": 0,
                "matchTested": 0,
                "children": [
                    {
                        "type": "SORT_MERGE",
                        "works": 33,
                        "yields": 0,
                        "unyields": 0,
                        "invalidates": 0,
                        "advanced": 10,
                        "needTime": 23,
                        "needFetch": 0,
                        "isEOF": 0,
                        "dupsTested": 22,
                        "dupsDropped": 2,
                        "forcedFetches": 0,
                        "children": [
                            {
                                "type": "IXSCAN",
                                "works": 1,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 1,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"52ddd476347b1b8\", \"52ddd476347b1b8\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 2,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 1,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 1,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"52ddd479eab21d8\", \"52ddd479eab21d8\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 2,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 2,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 2,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"52ddd476347b1b8\", \"52ddd476347b1b8\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 3,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 1,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 1,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"52ddd4783b635c3\", \"52ddd4783b635c3\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 2,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 2,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 2,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"52ddd479eab21d8\", \"52ddd479eab21d8\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 3,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 3,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 3,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 1,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"531e84d1713f6be\", \"531e84d1713f6be\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 3,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 1,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 1,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 1,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"531e84d39af5400\", \"531e84d39af5400\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 1,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 1,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 1,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 1,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"531e84d58833772\", \"531e84d58833772\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 1,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 1,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 1,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 1,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"531e85792aaefa3\", \"531e85792aaefa3\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 1,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 2,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 2,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 1,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"531e94d8e0c3a62\", \"531e94d8e0c3a62\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [\"F\", \"F\"], [\"P\", \"P\"]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 3,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 6,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 6,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 0,
                                "keyPattern": "{ owner: 1, ca: -1, permission: 1 }",
                                "boundsVerbose": "field #0['owner']: [\"52ddd47ba6c59d4\", \"52ddd47ba6c59d4\"], field #1['ca']: [MaxKey, MinKey], field #2['permission']: [MinKey, MaxKey]",
                                "isMultiKey": 0,
                                "yieldMovedCursor": 0,
                                "dupsTested": 0,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 7,
                                "children": [
 
                                ]
                            },
                            {
                                "type": "IXSCAN",
                                "works": 2,
                                "yields": 0,
                                "unyields": 0,
                                "invalidates": 0,
                                "advanced": 1,
                                "needTime": 0,
                                "needFetch": 0,
                                "isEOF": 1,
                                "keyPattern": "{ designate: 1, ca: -1 }",
                                "boundsVerbose": "field #0['designate']: [\"52ddd47ba6c59d4\", \"52ddd47ba6c59d4\"], field #1['ca']: [MaxKey, MinKey]",
                                "isMultiKey": 1,
                                "yieldMovedCursor": 0,
                                "dupsTested": 1,
                                "dupsDropped": 0,
                                "seenInvalidated": 0,
                                "matchTested": 0,
                                "keysExamined": 1,
                                "children": [
 
                                ]
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

Comment by Asya Kamsky [ 28/Apr/14 ]

xres74 would you please provide the output of your query with explain() - I cannot reproduce your report.

Comment by Edward [ 27/Apr/14 ]

Data structure of the above looks like:

{
    "_id": "...",
    "owner": "001",
    "permission": "public",
    "designate": [ ]
    "msg": "Nice dress!",
    "create_time": 1390459269
}
{
    "_id": "...",
    "owner": "005",
    "permission": "designate",
    "designate": ["001", "007"]
    "msg": "Nice day!",
    "create_time": 1390837209
}

Generated at Thu Feb 08 03:32:46 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.