MongoDB 2d index query with an additional sorting field - performance issue

Question

I have a compound index in a 2d and normal field:

"key" : {
            "_meta.loc.coordinates" : "2d",
            "_meta.unifiedAt" : 1
        },

Then I want to execute the following query with sorting:

db.static_booking
  .find({ "_meta.loc.coordinates": { "$geoWithin": { "$box": [ [ -0.70724367, 51.13817111 ], [ 0.45524367, 51.86182889 ] ] } } }, {itemId: 1})
  .sort({'_meta.unifiedAt': 1})
  .limit(100)

The problem I have is that it takes a very long time to return the results. Checking the explain, I see that it does not use the compound index created:

> db.static_booking.find({ "_meta.loc.coordinates": { "$geoWithin": { "$box": [ [ -0.70724367, 51.13817111 ], [ 0.45524367, 51.86182889 ] ] } } }, {itemId: 1}).sort({'_meta.unifiedAt': 1}).limit(100).explain()
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "seetransparent.static_booking",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "_meta.loc.coordinates" : {
                "$geoWithin" : {
                    "$box" : [
                        [
                            -0.70724367,
                            51.13817111
                        ],
                        [
                            0.45524367,
                            51.86182889
                        ]
                    ]
                }
            }
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 100,
            "inputStage" : {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "itemId" : 1
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "_meta.loc.coordinates" : {
                            "$geoWithin" : {
                                "$box" : [
                                    [
                                        -0.70724367,
                                        51.13817111
                                    ],
                                    [
                                        0.45524367,
                                        51.86182889
                                    ]
                                ]
                            }
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "_meta.unifiedAt" : 1
                        },
                        "indexName" : "_meta.unifiedAt_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "_meta.unifiedAt" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "_meta.unifiedAt" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "itemId" : 1
                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "_meta.unifiedAt" : 1
                    },
                    "limitAmount" : 100,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "filter" : {
                                "_meta.loc.coordinates" : {
                                    "$geoWithin" : {
                                        "$box" : [
                                            [
                                                -0.70724367,
                                                51.13817111
                                            ],
                                            [
                                                0.45524367,
                                                51.86182889
                                            ]
                                        ]
                                    }
                                }
                            },
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "_meta.loc.coordinates" : "2d"
                                },
                                "indexName" : "_meta.loc.coordinates_2d",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "_meta.loc.coordinates" : [
                                        "[BinData(128, 6EBA300000000000), BinData(128, 6EBA3FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA680000000000), BinData(128, 6EBA6BFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA6C0000000000), BinData(128, 6EBA6FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA700000000000), BinData(128, 6EBA7FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA900000000000), BinData(128, 6EBA9FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAB00000000000), BinData(128, 6EBABFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAC00000000000), BinData(128, 6EBACFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAD00000000000), BinData(128, 6EBADFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAE00000000000), BinData(128, 6EBAEFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAF00000000000), BinData(128, 6EBAFFFFFFFFFFFF)]",
                                        "[BinData(128, C410100000000000), BinData(128, C4101FFFFFFFFFFF)]",
                                        "[BinData(128, C410300000000000), BinData(128, C4103FFFFFFFFFFF)]",
                                        "[BinData(128, C410400000000000), BinData(128, C4104FFFFFFFFFFF)]",
                                        "[BinData(128, C410500000000000), BinData(128, C4105FFFFFFFFFFF)]",
                                        "[BinData(128, C410600000000000), BinData(128, C4106FFFFFFFFFFF)]",
                                        "[BinData(128, C410700000000000), BinData(128, C4107FFFFFFFFFFF)]"
                                    ]
                                }
                            }
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "itemId" : 1
                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "_meta.unifiedAt" : 1
                    },
                    "limitAmount" : 100,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "filter" : {
                                "_meta.loc.coordinates" : {
                                    "$geoWithin" : {
                                        "$box" : [
                                            [
                                                -0.70724367,
                                                51.13817111
                                            ],
                                            [
                                                0.45524367,
                                                51.86182889
                                            ]
                                        ]
                                    }
                                }
                            },
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "_meta.loc.coordinates" : "2d",
                                    "_meta.delisted" : 1
                                },
                                "indexName" : "_meta.loc.coordinates_2d__meta.delisted_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "_meta.loc.coordinates" : [
                                        "[BinData(128, 6EBA300000000000), BinData(128, 6EBA3FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA680000000000), BinData(128, 6EBA6BFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA6C0000000000), BinData(128, 6EBA6FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA700000000000), BinData(128, 6EBA7FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA900000000000), BinData(128, 6EBA9FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAB00000000000), BinData(128, 6EBABFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAC00000000000), BinData(128, 6EBACFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAD00000000000), BinData(128, 6EBADFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAE00000000000), BinData(128, 6EBAEFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAF00000000000), BinData(128, 6EBAFFFFFFFFFFFF)]",
                                        "[BinData(128, C410100000000000), BinData(128, C4101FFFFFFFFFFF)]",
                                        "[BinData(128, C410300000000000), BinData(128, C4103FFFFFFFFFFF)]",
                                        "[BinData(128, C410400000000000), BinData(128, C4104FFFFFFFFFFF)]",
                                        "[BinData(128, C410500000000000), BinData(128, C4105FFFFFFFFFFF)]",
                                        "[BinData(128, C410600000000000), BinData(128, C4106FFFFFFFFFFF)]",
                                        "[BinData(128, C410700000000000), BinData(128, C4107FFFFFFFFFFF)]"
                                    ],
                                    "_meta.delisted" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "itemId" : 1
                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "_meta.unifiedAt" : 1
                    },
                    "limitAmount" : 100,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "filter" : {
                                "_meta.loc.coordinates" : {
                                    "$geoWithin" : {
                                        "$box" : [
                                            [
                                                -0.70724367,
                                                51.13817111
                                            ],
                                            [
                                                0.45524367,
                                                51.86182889
                                            ]
                                        ]
                                    }
                                }
                            },
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "_meta.loc.coordinates" : "2d",
                                    "_meta.delisted" : 1,
                                    "_id" : 1
                                },
                                "indexName" : "_meta.loc.coordinates_2d__meta.delisted_1__id_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "_meta.loc.coordinates" : [
                                        "[BinData(128, 6EBA300000000000), BinData(128, 6EBA3FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA680000000000), BinData(128, 6EBA6BFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA6C0000000000), BinData(128, 6EBA6FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA700000000000), BinData(128, 6EBA7FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA900000000000), BinData(128, 6EBA9FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAB00000000000), BinData(128, 6EBABFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAC00000000000), BinData(128, 6EBACFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAD00000000000), BinData(128, 6EBADFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAE00000000000), BinData(128, 6EBAEFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAF00000000000), BinData(128, 6EBAFFFFFFFFFFFF)]",
                                        "[BinData(128, C410100000000000), BinData(128, C4101FFFFFFFFFFF)]",
                                        "[BinData(128, C410300000000000), BinData(128, C4103FFFFFFFFFFF)]",
                                        "[BinData(128, C410400000000000), BinData(128, C4104FFFFFFFFFFF)]",
                                        "[BinData(128, C410500000000000), BinData(128, C4105FFFFFFFFFFF)]",
                                        "[BinData(128, C410600000000000), BinData(128, C4106FFFFFFFFFFF)]",
                                        "[BinData(128, C410700000000000), BinData(128, C4107FFFFFFFFFFF)]"
                                    ],
                                    "_meta.delisted" : [
                                        "[MinKey, MaxKey]"
                                    ],
                                    "_id" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }
                    }
                }
            },
            {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "itemId" : 1
                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "_meta.unifiedAt" : 1
                    },
                    "limitAmount" : 100,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "filter" : {
                                "_meta.loc.coordinates" : {
                                    "$geoWithin" : {
                                        "$box" : [
                                            [
                                                -0.70724367,
                                                51.13817111
                                            ],
                                            [
                                                0.45524367,
                                                51.86182889
                                            ]
                                        ]
                                    }
                                }
                            },
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "_meta.loc.coordinates" : "2d",
                                    "_meta.unifiedAt" : 1
                                },
                                "indexName" : "_meta.loc.coordinates_2d__meta.unifiedAt_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "_meta.loc.coordinates" : [
                                        "[BinData(128, 6EBA300000000000), BinData(128, 6EBA3FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA680000000000), BinData(128, 6EBA6BFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA6C0000000000), BinData(128, 6EBA6FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA700000000000), BinData(128, 6EBA7FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBA900000000000), BinData(128, 6EBA9FFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAB00000000000), BinData(128, 6EBABFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAC00000000000), BinData(128, 6EBACFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAD00000000000), BinData(128, 6EBADFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAE00000000000), BinData(128, 6EBAEFFFFFFFFFFF)]",
                                        "[BinData(128, 6EBAF00000000000), BinData(128, 6EBAFFFFFFFFFFFF)]",
                                        "[BinData(128, C410100000000000), BinData(128, C4101FFFFFFFFFFF)]",
                                        "[BinData(128, C410300000000000), BinData(128, C4103FFFFFFFFFFF)]",
                                        "[BinData(128, C410400000000000), BinData(128, C4104FFFFFFFFFFF)]",
                                        "[BinData(128, C410500000000000), BinData(128, C4105FFFFFFFFFFF)]",
                                        "[BinData(128, C410600000000000), BinData(128, C4106FFFFFFFFFFF)]",
                                        "[BinData(128, C410700000000000), BinData(128, C4107FFFFFFFFFFF)]"
                                    ],
                                    "_meta.unifiedAt" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        ]
    },
    "serverInfo" : {
        "host" : "production",
        "port" : 27017,
        "version" : "3.4.3",
        "gitVersion" : "f07437fb5a6cca07c10bafa78365456eb1d6d5e1"
    },
    "ok" : 1
}

As it is not using the index that I created, I try to use the hint method to force the index, but then I get an error in the result:

> db.static_booking.find({ "_meta.loc.coordinates": { "$geoWithin": { "$box": [ [ -0.70724367, 51.13817111 ], [ 0.45524367, 51.86182889 ] ] } } }, {itemId: 1}).sort({'_meta.unifiedAt': 1}).limit(100).hint({'_meta.loc.coordinates': '2d', '_meta.unifiedAt': 1})
Error: error: {
    "ok" : 0,
    "errmsg" : "Executor error during find command: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.",
    "code" : 96,
    "codeName" : "OperationFailed"
}

As long as I use a limit larger than 2, then the error will appear. How can I solve this issue? Thanks.

The last plan in the rejectedPlans section is the one which uses your compound index as you were hoping, but it doesn't explain why that plan was rejected. Could you try asking it to explain, but with the verbosity mode set to "allPlansExecution", to see if that tells you any more? — Vince Bowdren
– Vince Bowdren, Commented Apr 24, 2017 at 9:19

SqlWorldWide · Accepted Answer · 2017-04-23 21:56:44Z

1

You are hitting the limit of in memory sort which is 32MB.

Sort Operations

If MongoDB cannot use an index to get documents in the requested sort order, the combined size of all documents in the sort operation, plus a small overhead, must be less than 32 megabytes.

You can add an index with leading column _meta.unifiedAt.

edited Apr 23, 2017 at 21:56

answered Apr 23, 2017 at 14:07

SqlWorldWide

13.7k3 gold badges31 silver badges55 bronze badges

Add a comment |

Stack Exchange Network

MongoDB 2d index query with an additional sorting field - performance issue

1 Answer 1

Your Answer

Hot Network Questions

MongoDB 2d index query with an additional sorting field - performance issue

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions