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

Mongodb distinct not using indexes with query ?

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Done
    • 3.0.11, 3.2.4
    • None
    • Querying
    • None
    • ALL

    Description

      Here is my docs look like:

      shifudaoRs:SECONDARY> db.equipment.rtdata.find()
      { "_id" : ObjectId("5610093899695e7062add4ac"), "timestamp" : NumberLong("1443891603962"), "1" : 0, "2" : 0, "3" : 26, "4" : 2530971136, "5" : 655369984, "38" : 0, "37" : 0, "36" : 1, "35" : 0, "34" : 0, "33" : 0, "32" : 1, "31" : 1, "54" : 0, "53" : 0, "52" : 0, "51" : 0, "50" : 0, "49" : 0, "48" : 0, "47" : 1, "70" : 1, "69" : 0, "68" : 0, "67" : 1, "66" : 0, "65" : 1, "64" : 0, "63" : 1, "isLoad" : 0, "isRun" : 0, "power" : 41.25, "6" : 10000, "7" : 10000, "8" : 20545, "9" : 0, "10" : 1280, "86" : 0, "85" : 0, "84" : 0, "83" : 0, "82" : 0, "81" : 0, "80" : 0, "79" : 0, "src" : NumberLong(33) }
      { "_id" : ObjectId("5610094299695e7062add4c4"), "timestamp" : NumberLong("1443891613010"), "1" : 0, "2" : 0, "3" : 26, "4" : 2530971136, "5" : 655369984, "38" : 0, "37" : 0, "36" : 1, "35" : 0, "34" : 0, "33" : 0, "32" : 1, "31" : 1, "54" : 0, "53" : 0, "52" : 0, "51" : 0, "50" : 0, "49" : 0, "48" : 0, "47" : 1, "70" : 1, "69" : 0, "68" : 0, "67" : 1, "66" : 0, "65" : 1, "64" : 0, "63" : 1, "isLoad" : 0, "isRun" : 0, "power" : 41.25, "6" : 10000, "7" : 10000, "8" : 20545, "9" : 0, "10" : 1280, "86" : 0, "85" : 0, "84" : 0, "83" : 0, "82" : 0, "81" : 0, "80" : 0, "79" : 0, "src" : NumberLong(33) }
      { "_id" : ObjectId("5610094c99695e7062add4da"), "timestamp" : NumberLong("1443891624859"), "1" : 0, "2" : 0, "3" : 26, "4" : 2530971136, "5" : 655369984, "38" : 0, "37" : 0, "36" : 1, "35" : 0, "34" : 0, "33" : 0, "32" : 1, "31" : 1, "54" : 0, "53" : 0, "52" : 0, "51" : 0, "50" : 0, "49" : 0, "48" : 0, "47" : 1, "70" : 1, "69" : 0, "68" : 0, "67" : 1, "66" : 0, "65" : 1, "64" : 0, "63" : 1, "isLoad" : 0, "isRun" : 0, "power" : 41.25, "6" : 10000, "7" : 10000, "8" : 20545, "9" : 0, "10" : 1280, "86" : 0, "85" : 0, "84" : 0, "83" : 0, "82" : 0, "81" : 0, "80" : 0, "79" : 0, "src" : NumberLong(33) }
      { "_id" : ObjectId("5610095699695e7062add4f1"), "timestamp" : NumberLong("1443891634234"), "1" : 0, "2" : 0, "3" : 26, "4" : 2530971136, "5" : 655369984, "38" : 0, "37" : 0, "36" : 1, "35" : 0, "34" : 0, "33" : 0, "32" : 1, "31" : 1, "54" : 0, "53" : 0, "52" : 0, "51" : 0, "50" : 0, "49" : 0, "48" : 0, "47" : 1, "70" : 1, "69" : 0, "68" : 0, "67" : 1, "66" : 0, "65" : 1, "64" : 0, "63" : 1, "isLoad" : 0, "isRun" : 0, "power" : 41.25, "6" : 10000, "7" : 10000, "8" : 20545, "9" : 0, "10" : 1280, "86" : 0, "85" : 0, "84" : 0, "83" : 0, "82" : 0, "81" : 0, "80" : 0, "79" : 0, "src" : NumberLong(33) }
      

      And here is my indexes:

      shifudaoRs:SECONDARY> db.equipment.rtdata.getIndexes()
      [
      	{
      		"v" : 1,
      		"key" : {
      			"_id" : 1
      		},
      		"name" : "_id_",
      		"ns" : "aircareRealtimeDB.equipment.rtdata"
      	},
      	{
      		"v" : 1,
      		"key" : {
      			"src" : 1,
      			"timestamp" : -1
      		},
      		"name" : "src_1_timestamp_-1",
      		"ns" : "aircareRealtimeDB.equipment.rtdata",
      		"background" : true
      	},
      	{
      		"v" : 1,
      		"key" : {
      			"src" : 1
      		},
      		"name" : "src_1",
      		"ns" : "aircareRealtimeDB.equipment.rtdata",
      		"background" : true
      	},
      	{
      		"v" : 1,
      		"key" : {
      			"timestamp" : -1
      		},
      		"name" : "timestamp_-1",
      		"ns" : "aircareRealtimeDB.equipment.rtdata",
      		"background" : true
      	}
      ]
      

      I want distinct("src") by timestamp duration.

      I find only distinct the whole src executed very fast:

      shifudaoRs:SECONDARY> db.runCommand({ distinct: 'equipment.rtdata',key:'src'})
      {
      	"values" : [
            ... SNIP ...
      	],
      	"stats" : {
      		"n" : 467,
      		"nscanned" : 467,
      		"nscannedObjects" : 0,
      		"timems" : 17,
      		"planSummary" : "DISTINCT { src: 1 }"
      	},
      	"ok" : 1
      }
      

      But when I add a query about the timestamp duration, will be very slow.

      db.runCommand({ distinct: 'equipment.rtdata',key:'src', query: {timestamp: {$gte:0, $lt:999999999999999999999}}})
      

      Of course this will be the same result, but execute very slow. And have not finished for now.

      shifudaoRs:SECONDARY> db.currentOp()
      {
      	"inprog" : [
      		{
      			"desc" : "conn51410",
      			"threadId" : "0xf1dfd40",
      			"connectionId" : 51410,
      			"opid" : 20780367,
      			"active" : true,
      			"secs_running" : 2304,
      			"microsecs_running" : NumberLong("2304321577"),
      			"op" : "query",
      			"ns" : "aircareRealtimeDB.equipment.rtdata",
      			"query" : {
      				"distinct" : "equipment.rtdata",
      				"key" : "src",
      				"query" : {
      					"timestamp" : {
      						"$gte" : 0,
      						"$lt" : 1e+21
      					}
      				}
      			},
      			"client" : "127.0.0.1:50387",
      			"numYields" : 452037,
      			"locks" : {
      				"Global" : "r",
      				"Database" : "r",
      				"Collection" : "r"
      			},
      			"waitingForLock" : false,
      			"lockStats" : {
      				"Global" : {
      					"acquireCount" : {
      						"r" : NumberLong(904076)
      					},
      					"acquireWaitCount" : {
      						"r" : NumberLong(4683)
      					},
      					"timeAcquiringMicros" : {
      						"r" : NumberLong(10905279)
      					}
      				},
      				"Database" : {
      					"acquireCount" : {
      						"r" : NumberLong(452038)
      					}
      				},
      				"Collection" : {
      					"acquireCount" : {
      						"r" : NumberLong(452038)
      					}
      				}
      			}
      		}
      	]
      }
      

      Maybe mongodb not use the "src" indexes? How could I optimize this distinct ?

      Attachments

        Activity

          People

            kelsey.schubert@mongodb.com Kelsey Schubert
            abcfy2 Feng Yu
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: