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

$or does not use indexes when combined with sort()

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Duplicate
    • Icon: Major - P3 Major - P3
    • None
    • 1.6.0
    • None
    • Debian Lenny 4.0, amd64
    • Linux

    Description

      // First of all, my indexes:

      db.notes.getIndexes() [

      {
      "name" : "id",
      "ns" : "qix.notes",
      "key" :

      { "_id" : 1 }

      },
      {
      "ns" : "qix.notes",
      "name" : "shared_to_1",
      "key" :

      { "shared_to" : 1 }

      },
      {
      "ns" : "qix.notes",
      "name" : "tags_1",
      "key" :

      { "tags" : 1 }

      },
      {
      "ns" : "qix.notes",
      "name" : "owner_account_id_1",
      "key" :

      { "owner_account_id" : 1 }

      },
      {
      "ns" : "qix.notes",
      "name" : "location_2d",
      "key" :

      { "location" : "2d" }

      }
      ]

      // No sorting - $or is fast

      > db.notes.find({$or : [

      {owner_account_id : ObjectId("0000000000000000000c7e98")}

      ,

      {shared_to : ObjectId("0000000000000000000c7e98")}

      ] }).explain()
      {
      "clauses" : [
      {
      "cursor" : "BtreeCursor owner_account_id_1",
      "nscanned" : 255,
      "nscannedObjects" : 255,
      "n" : 255,
      "millis" : 0,
      "indexBounds" :

      { "owner_account_id" : [ [ ObjectId("0000000000000000000c7e98"), ObjectId("0000000000000000000c7e98") ] ] }

      ,
      "allPlans" : [
      {
      "cursor" : "BtreeCursor owner_account_id_1",
      "indexBounds" :

      { "owner_account_id" : [ [ ObjectId("0000000000000000000c7e98"), ObjectId("0000000000000000000c7e98") ] ] }

      }
      ]
      },
      {
      "cursor" : "BtreeCursor shared_to_1",
      "nscanned" : 0,
      "nscannedObjects" : 0,
      "n" : 0,
      "millis" : 0,
      "indexBounds" :

      { "shared_to" : [ [ ObjectId("0000000000000000000c7e98"), ObjectId("0000000000000000000c7e98") ] ] }

      ,
      "allPlans" : [
      {
      "cursor" : "BtreeCursor shared_to_1",
      "indexBounds" :

      { "shared_to" : [ [ ObjectId("0000000000000000000c7e98"), ObjectId("0000000000000000000c7e98") ] ] }

      }
      ]
      }
      ],
      "nscanned" : 255,
      "nscanneObjects" : 255,
      "n" : 255,
      "millis" : 0
      }

      // Sort + limit, $or is slow:

      > db.notes.find({$or : [

      {owner_account_id : ObjectId("0000000000000000000c7e98")}

      ,

      {shared_to : ObjectId("0000000000000000000c7e98")}

      ] }).limit(20).sort(

      {created_at:1}

      ).explain()
      {
      "cursor" : "BasicCursor",
      "nscanned" : 822547,
      "nscannedObjects" : 822547,
      "n" : 20,
      "scanAndOrder" : true,
      "millis" : 1505,
      "indexBounds" : {

      }
      }

      Attachments

        Activity

          People

            aaron Aaron Staple
            niallo Niall O'Higgins
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: