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

query projection spec using 'true' instead of '1' prevents covered index query

      Update (aaron)

      A query projection a:true should be equivalent to a:1, however specification of a true value prevents covered index queries apparently due to the following line:

                  if ( ! e.isNumber() )
                      _hasNonSimple = true;
      

      Test

      c = db.c;
      c.drop();
      
      c.save( { a:5 } );
      assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:1 } ).toArray()[ 0 ] );
      assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:true } ).toArray()[ 0 ] );
      
      c.ensureIndex( { a:1 } );
      assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:1 } ).toArray()[ 0 ] );
      assert( c.find( { a:5 }, { _id:0, a:1 } ).explain().indexOnly );
      assert.eq( { a:5 }, c.find( {}, { _id:0, a:true } ).toArray()[ 0 ] );
      
      // Fails because 'true' prevents doing a covered index query.
      assert( c.find( { a:5 }, { _id:0, a:true } ).explain().indexOnly );
      

      ------------------------------------------------------------

      I'm working on a collection consisting of elements like the one below:

      {
      "_id" : ObjectId("4ddec579b47cc1e6024d15a9"),
      "CH_price" : 0,
      "TR_price" : 312,
      "TW_price" : null,
      "city_code" : "BER",
      "currency" : "EUR",
      "max_chd" : 0,
      "max_chd_age" : 0,
      "max_pax" : 3,
      "meal" : "BB",
      "min_chd" : 0,
      "min_chd_age" : 0,
      "min_nights" : 0,
      "min_pax" : 3,
      "modified" : ISODate("2011-05-26T21:24:31.871Z"),
      "original_room" : "DZ",
      "rate_code" : "BER466_DZ",
      "room" : "DR",
      "source" : "FTI",
      "tlc" : "BER",
      "valid_from" : ISODate("2011-05-28T00:00:00Z"),
      "valid_to" : ISODate("2011-05-29T00:00:00Z"),
      "hotel_name" : "EUROSTARS BERLIN",
      "category" : 5,
      "city_name" : "BerlĂ­n",
      "geo" : [
      52.520870208740234,
      13.389501571655273
      ],
      "name" : "Standard",
      "mh_code" : "BER000AJ",
      "mhr_code" : "BER000AJ01",
      "hotel_id" : "BER466_CITY"
      }

      which has following indexes defined:

      {
      "name" : "id",
      "ns" : "xenia_pricing.mh_rate_prices",
      "key" :

      { "_id" : 1 }

      },
      {
      "_id" : ObjectId("4d81c0f20c42d00e2c1a4e18"),
      "name" : "rate_code_1_min_nights_1_valid_from_1_valid_to_1",
      "ns" : "xenia_pricing.mh_rate_prices",
      "key" :

      { "rate_code" : 1, "min_nights" : 1, "valid_from" : 1, "valid_to" : 1 }

      }

      After submitting a sample query:

      db.mh_rate_prices.find({ "rate_code" :

      { "$in" : ["BER466_DZ"] }

      , "min_nights" :

      { "$lte" : 2 }

      , "$or" : [{ "valid_from" : { "$lte" :

      { "$date" : 1309071377602 }

      }, "valid_to" : { "$gte" :

      { "$date" : 1309071377602 }

      } }, { "valid_from" : { "$lte" :

      { "$date" : 1309244177602 }

      }, "valid_to" : { "$gte" :

      { "$date" : 1309244177602 }

      } }, { "valid_from" : { "$gte" :

      { "$date" : 1309071377602 }

      }, "valid_to" : { "$lte" :

      { "$date" : 1309244177602 }

      } }] }).explain()

      .explain() indicated that "indexOnly" : false, which suggests that the query was not covered by index:

      {
      "clauses" : [
      {
      "cursor" : "BtreeCursor valid_from_1_rate_code_1_min_nights_1_valid_to_1",
      "nscanned" : 0,
      "nscannedObjects" : 0,
      "n" : 0,
      "millis" : 0,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "isMultiKey" : false,
      "indexOnly" : false,
      "indexBounds" : {
      "valid_from" : [
      [

      { "$minElement" : 1 }

      ,

      { "$date" : 1309071377602 }

      ]
      ],
      "rate_code" : [
      [
      "BER466_DZ",
      "BER466_DZ"
      ]
      ],
      "min_nights" : [
      [
      -1.7976931348623157e+308,
      2
      ]
      ],
      "valid_to" : [
      [

      { "$date" : 1309071377602 }

      ,

      { "$maxElement" : 1 }

      ]
      ]
      }
      },
      {
      "cursor" : "BtreeCursor valid_from_1_rate_code_1_min_nights_1_valid_to_1",
      "nscanned" : 0,
      "nscannedObjects" : 0,
      "n" : 0,
      "millis" : 0,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "isMultiKey" : false,
      "indexOnly" : false,
      "indexBounds" : {
      "valid_from" : [
      [

      { "$date" : 1309071377602 }

      ,

      { "$date" : 1309244177602 }

      ]
      ],
      "rate_code" : [
      [
      "BER466_DZ",
      "BER466_DZ"
      ]
      ],
      "min_nights" : [
      [
      -1.7976931348623157e+308,
      2
      ]
      ],
      "valid_to" : [
      [

      { "$date" : 1309244177602 }

      ,

      { "$maxElement" : 1 }

      ]
      ]
      }
      },
      {
      "cursor" : "BtreeCursor rate_code_1_min_nights_1_valid_from_1_valid_to_1",
      "nscanned" : 59,
      "nscannedObjects" : 0,
      "n" : 0,
      "millis" : 4,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "isMultiKey" : false,
      "indexOnly" : false,
      "indexBounds" : {
      "rate_code" : [
      [
      "BER466_DZ",
      "BER466_DZ"
      ]
      ],
      "min_nights" : [
      [
      -1.7976931348623157e+308,
      2
      ]
      ],
      "valid_from" : [
      [

      { "$date" : 1309071377602 }

      ,

      { "$maxElement" : 1 }

      ]
      ],
      "valid_to" : [
      [

      { "$minElement" : 1 }

      ,

      { "$date" : 1309244177602 }

      ]
      ]
      }
      }
      ],
      "nscanned" : 59,
      "nscannedObjects" : 0,
      "n" : 0,
      "millis" : 4
      }

      What is the reason of that behaviour and how should I define indexes to avoid long response time (up to 5 seconds for some queries)?

        1. server3156.js
          0.6 kB
          Benety Goh

            Assignee:
            benety.goh@mongodb.com Benety Goh
            Reporter:
            styniu Sebastian Styka
            Votes:
            2 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: