Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-3310

Query optimizer should efficiently handle $in and sort with compound index.

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5.5
    • Component/s: Querying
    • Labels:

      Description

      Suppose you have an index on

      {friend_id: 1, date: -1}

      Then, suppose you have a query like this:

      db.things.find({ friend_id: {$in: [lots_of_ids]}}).sort(

      {date: -1}

      ).limit(20)

      Currently, this does a scanAndOrder and scans quite a few documents. Can we optimize?

      1. server3310.js
        0.7 kB
        Benety Goh

        Issue Links

          Activity

          Hide
          haeusler Michael Häusler added a comment -

          This optimization is really critical in many use cases. E.g., find the 10 latest movies of your favorite actors.

          {
            "title" : "MongoDB - The Movie",
            "actorIds" : [7, 25, 42],
            "date" : ISODate("2013-05-23T00:00:00Z")
          }

          db.movies.ensureIndex({ "actorIds" : 1, "date" : -1 });
           
          db.movies.find({ "actorIds" : { "$in" : [42, 451] } }).sort({ "date" : -1 }).limit(10);

          For this use case, it is important that the optimization will also work with a compound multikey index.

          Show
          haeusler Michael Häusler added a comment - This optimization is really critical in many use cases. E.g., find the 10 latest movies of your favorite actors. { "title" : "MongoDB - The Movie", "actorIds" : [7, 25, 42], "date" : ISODate("2013-05-23T00:00:00Z") } db.movies.ensureIndex({ "actorIds" : 1, "date" : -1 });   db.movies.find({ "actorIds" : { "$in" : [42, 451] } }).sort({ "date" : -1 }).limit(10); For this use case, it is important that the optimization will also work with a compound multikey index.
          Hide
          hari.khalsa@10gen.com Hari Khalsa (Inactive) added a comment -
          Show
          hari.khalsa@10gen.com Hari Khalsa (Inactive) added a comment - See SERVER-1205
          Hide
          xres74 Edward added a comment -

          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.

          Show
          xres74 Edward added a comment - 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.
          Hide
          fastest963 James Hartig added a comment -

          Any chance we can get this backported to 2.2.x?

          Show
          fastest963 James Hartig added a comment - Any chance we can get this backported to 2.2.x?
          Hide
          milkie Eric Milkie added a comment -

          Unfortunately, the query optimizer code is completely different in 2.2.x, so backporting will not be possible.

          Show
          milkie Eric Milkie added a comment - Unfortunately, the query optimizer code is completely different in 2.2.x, so backporting will not be possible.

            People

            • Votes:
              51 Vote for this issue
              Watchers:
              61 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Days since reply:
                2 years, 37 weeks ago
                Date of 1st Reply: