Compound index is rejected when i use sort by _id field

I have a collection that contains:

{  _id: 1, reportId: "a",  accountId: "1"  },
{  _id: 2, reportId: "b",  accountId: "1"  }

I have created compound Index:

db.collection.createIndex({ reportId: 1, accountId: 1 });

Executing query:

db.collection.find({ reportId: "a", accountId: "1" }).sort({ _id: 1 });

This query uses _id index instead of compound index, and compound index in in rejected plain,


FETCH Details
{
 "stage": "FETCH",
 "filter": {
  "$and": [
   {
    "accountId": {
     "$eq": "1"
    }
   },
   {
    "reportId": {
     "$eq": "a"
    }
   }
  ]
 },
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 4,
 "advanced": 1,
 "needTime": 1,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "docsExamined": 2,
 "alreadyHasObj": 0
}

IXSCAN Details
{
 "stage": "IXSCAN",
 "nReturned": 2,
 "executionTimeMillisEstimate": 0,
 "works": 3,
 "advanced": 2,
 "needTime": 0,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "keyPattern": {
  "_id": 1
 },
 "indexName": "_id_",
 "isMultiKey": false,
 "multiKeyPaths": {
  "_id": []
 },
 "isUnique": true,
 "isSparse": false,
 "isPartial": false,
 "indexVersion": 2,
 "direction": "forward",
 "indexBounds": {
  "_id": [
   "[MinKey, MaxKey]"
  ]
 },
 "keysExamined": 2,
 "seeks": 1,
 "dupsTested": 0,
 "dupsDropped": 0,
 "parentName": "FETCH"
}

Raw JSON of explain
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "sample.index1",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "accountId" : {
                        "$eq" : "1"
                    }
                }, 
                {
                    "reportId" : {
                        "$eq" : "a"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "$and" : [ 
                    {
                        "accountId" : {
                            "$eq" : "1"
                        }
                    }, 
                    {
                        "reportId" : {
                            "$eq" : "a"
                        }
                    }
                ]
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "_id" : 1
                },
                "indexName" : "_id_",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "_id" : []
                },
                "isUnique" : true,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "_id" : [ 
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "_id" : 1
                },
                "memLimit" : 104857600,
                "type" : "simple",
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "reportId" : 1.0,
                            "accountId" : 1.0
                        },
                        "indexName" : "reportId_1_accountId_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "reportId" : [],
                            "accountId" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "reportId" : [ 
                                "[\"a\", \"a\"]"
                            ],
                            "accountId" : [ 
                                "[\"1\", \"1\"]"
                            ]
                        }
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 2,
        "totalDocsExamined" : 2,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "$and" : [ 
                    {
                        "accountId" : {
                            "$eq" : "1"
                        }
                    }, 
                    {
                        "reportId" : {
                            "$eq" : "a"
                        }
                    }
                ]
            },
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 4,
            "advanced" : 1,
            "needTime" : 1,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 2,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 2,
                "executionTimeMillisEstimate" : 0,
                "works" : 3,
                "advanced" : 2,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "keyPattern" : {
                    "_id" : 1
                },
                "indexName" : "_id_",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "_id" : []
                },
                "isUnique" : true,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "_id" : [ 
                        "[MinKey, MaxKey]"
                    ]
                },
                "keysExamined" : 2,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "vt",
        "port" : 27017,
        "version" : "4.4.5",
        "gitVersion" : "ff5cb77101b052fa02da43b8538093486cf9b3f7"
    },
    "ok" : 1.0
}

Its really required to add _id field in compound index?

Do you only have 2 documents in the collection? I asked because, if I understand correctly, the query planner execute all plans at first until one gives better performance and since you are sorting may be it is more efficient to use _id with so little number of documents.

If you really only have 2 documents, try to populate with extra documents you may delete later, to see if it corrects.

1 Like

Thank you for pointing out, i have added more documents and it shows that compound index is used,

SORT Details
{
 "stage": "SORT",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 5,
 "advanced": 1,
 "needTime": 2,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "sortPattern": {
  "_id": 1
 },
 "memLimit": 104857600,
 "type": "simple",
 "totalDataSizeSorted": 67,
 "usedDisk": false
}

FETCH Details
{
 "stage": "FETCH",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 2,
 "advanced": 1,
 "needTime": 0,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "docsExamined": 1,
 "alreadyHasObj": 0,
 "parentName": "SORT"
}

IXSCAN
{
 "stage": "IXSCAN",
 "nReturned": 1,
 "executionTimeMillisEstimate": 0,
 "works": 2,
 "advanced": 1,
 "needTime": 0,
 "needYield": 0,
 "saveState": 0,
 "restoreState": 0,
 "isEOF": 1,
 "keyPattern": {
  "reportId": 1,
  "accountId": 1
 },
 "indexName": "reportId_1_accountId_1",
 "isMultiKey": false,
 "multiKeyPaths": {
  "reportId": [],
  "accountId": []
 },
 "isUnique": false,
 "isSparse": false,
 "isPartial": false,
 "indexVersion": 2,
 "direction": "forward",
 "indexBounds": {
  "reportId": [
   "[\"a\", \"a\"]"
  ],
  "accountId": [
   "[\"1\", \"1\"]"
  ]
 },
 "keysExamined": 1,
 "seeks": 1,
 "dupsTested": 0,
 "dupsDropped": 0,
 "parentName": "FETCH"
}

Raw JSON of expain
/* 1 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "sample.index1",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "accountId" : {
                        "$eq" : "1"
                    }
                }, 
                {
                    "reportId" : {
                        "$eq" : "a"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "_id" : 1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "reportId" : 1.0,
                        "accountId" : 1.0
                    },
                    "indexName" : "reportId_1_accountId_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "reportId" : [],
                        "accountId" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "reportId" : [ 
                            "[\"a\", \"a\"]"
                        ],
                        "accountId" : [ 
                            "[\"1\", \"1\"]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [ 
                        {
                            "accountId" : {
                                "$eq" : "1"
                            }
                        }, 
                        {
                            "reportId" : {
                                "$eq" : "a"
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "_id" : 1
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : []
                    },
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [ 
                            "[MinKey, MaxKey]"
                        ]
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 5,
            "advanced" : 1,
            "needTime" : 2,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "sortPattern" : {
                "_id" : 1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "totalDataSizeSorted" : 67,
            "usedDisk" : false,
            "inputStage" : {
                "stage" : "FETCH",
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 2,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "docsExamined" : 1,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 1,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 2,
                    "advanced" : 1,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 0,
                    "restoreState" : 0,
                    "isEOF" : 1,
                    "keyPattern" : {
                        "reportId" : 1.0,
                        "accountId" : 1.0
                    },
                    "indexName" : "reportId_1_accountId_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "reportId" : [],
                        "accountId" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "reportId" : [ 
                            "[\"a\", \"a\"]"
                        ],
                        "accountId" : [ 
                            "[\"1\", \"1\"]"
                        ]
                    },
                    "keysExamined" : 1,
                    "seeks" : 1,
                    "dupsTested" : 0,
                    "dupsDropped" : 0
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "vt",
        "port" : 27017,
        "version" : "4.4.5",
        "gitVersion" : "ff5cb77101b052fa02da43b8538093486cf9b3f7"
    },
    "ok" : 1.0
}

I have another question:

Why its SORT in memory? when _id have default unique index in order { _id: 1 },

Does it really require to add _id field in compound index?

Actually i found the answer of my question from below documentation, if i am not wrong,

Index Intersection and Sort:

Index intersection does not apply when the sort() operation requires an index completely separate from the query predicate.

So that is why its performing Blocking Sorts Operation and for prevention i must have to add _id field in compound index,

db.collection.createIndex({ reportId: 1, accountId: 1, _id: 1 });

Thank you @steevej

2 Likes