Sort on sharded cluster

[Oleksandr]

Hello
I'm trying to find the very first record in my sharded collection by Datetime.
I use this query: db.collection.find().sort({"insert_time": 1}).limit(1) and I have index on field 'insert_time'
And on one of 3 shards this query can't be finished. Just hangs for a long time

"query" : {

"find" : "event",

"projection" : {

"$sortKey" : {

"$meta" : "sortKey"

}

},

"sort" : {

"insert_time" : 1

},

"limit" : NumberLong(1),

"shardVersion" : [

Timestamp(43762, 1),

ObjectId("56fb9bc7ec7b8372c8aa3a7b")

]

},

"numYields" : 62950,

"locks" : {

"Global" : "r",

"Database" : "r",

"Collection" : "r"

},

"waitingForLock" : false,

In the same time when I just execute this query on this shard, it returns the result in less than one second.

What can be the cause of this problem?
Thank you.

[Asya Kamsky]

This field is not the shard key - before returning results the shard needs to filter out documents which don't "belong" on this shard. 

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/af680ad9-56fb-4cd4-b605-f19c940262fb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Chumakov Oleksandr]

Thank you very much for your answer.

Sorry, I didn't understand. I thought it should have sorted all documents on each shard, limited the result and then returned to primary shard which should have merge it and returned final result. Is it not right?

So if it not sharding key, I'm not able to accelerate this query. Right?

Thank you.

You received this message because you are subscribed to a topic in the Google Groups "mongodb-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mongodb-user/zjBXylr9yh8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mongodb-user+unsubscribe@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAOe6dJCw%2ByUTFHb25w0JBLh6xu-%2BqD%2BH0XuZ9FQmDdUGyeo9Ng%40mail.gmail.com.

[Asya Kamsky]

Is the sort on the shard supported by an index?  And is the shard key value also in that index?  If not, a compound index like that could help optimize performance of this query. 

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAHh77iyMVXKDE_6n6bTg_mG1KQC1nFTJXSKTu8C%2BoCk6MtbJDg%40mail.gmail.com.

[Chumakov Oleksandr]

Thank you, Asya
How could I know if the sort on the shard is supported by the index or not?

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAOe6dJC%2BAJUEAVfWJSnk_bk-%2BHDyGtkSDGV9UgzoFzBhUZ0fjw%40mail.gmail.com.

[Chumakov Oleksandr]

Here is the explain result also:

mongos> db.collection.find().sort({"insert_time": 1}).limit(1).explain()

{

"queryPlanner" : {

"mongosPlannerVersion" : 1,

"winningPlan" : {

"stage" : "SHARD_MERGE_SORT",

"shards" : [

{

"shardName" : "shard0",

"connectionString" : "shard0/mng10:27017,mng20:27017",

"serverInfo" : {

"host" : "mng10",

"port" : 27017,

"version" : "3.2.9-2.1",

"gitVersion" : "d497c754e9f679cd4e639bdffaa3cb31e54b7f04"

},

"plannerVersion" : 1,

"namespace" : "database.collection",

"indexFilterSet" : false,

"parsedQuery" : {

"$and" : [ ]

},

"winningPlan" : {

"stage" : "LIMIT",

"limitAmount" : 1,

"inputStage" : {

"stage" : "SHARDING_FILTER",

"inputStage" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

"insert_time" : NumberLong(1)

},

"indexName" : "insert_time_1",

"isMultiKey" : false,

"isUnique" : false,

"isSparse" : false,

"isPartial" : false,

"indexVersion" : 1,

"direction" : "forward",

"indexBounds" : {

"insert_time" : [

"[MinKey, MaxKey]"

]

}

}

}

}

},

"rejectedPlans" : [ ]

},

{

"shardName" : "shard1",

"connectionString" : "shard1/mng11:27017,mng21:27017",

"serverInfo" : {

"host" : "mng11",

"port" : 27017,

"version" : "3.2.9-2.1",

"gitVersion" : "d497c754e9f679cd4e639bdffaa3cb31e54b7f04"

},

"plannerVersion" : 1,

"namespace" : "database.collection",

"indexFilterSet" : false,

"parsedQuery" : {

"$and" : [ ]

},

"winningPlan" : {

"stage" : "LIMIT",

"limitAmount" : 1,

"inputStage" : {

"stage" : "SHARDING_FILTER",

"inputStage" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

"insert_time" : NumberLong(1)

},

"indexName" : "insert_time_1",

"isMultiKey" : false,

"isUnique" : false,

"isSparse" : false,

"isPartial" : false,

"indexVersion" : 1,

"direction" : "forward",

"indexBounds" : {

"insert_time" : [

"[MinKey, MaxKey]"

]

}

}

}

}

},

"rejectedPlans" : [ ]

},

{

"shardName" : "shard2",

"connectionString" : "shard2/mng12:27017,mng22:27017",

"serverInfo" : {

"host" : "mng22",

"port" : 27017,

"version" : "3.2.9-2.1",

"gitVersion" : "d497c754e9f679cd4e639bdffaa3cb31e54b7f04"

},

"plannerVersion" : 1,

"namespace" : "database.collection",

"indexFilterSet" : false,

"parsedQuery" : {

"$and" : [ ]

},

"winningPlan" : {

"stage" : "LIMIT",

"limitAmount" : 1,

"inputStage" : {

"stage" : "SHARDING_FILTER",

"inputStage" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

"insert_time" : NumberLong(1)

},

"indexName" : "insert_time_1",

"isMultiKey" : false,

"isUnique" : false,

"isSparse" : false,

"isPartial" : false,

"indexVersion" : 1,

"direction" : "forward",

"indexBounds" : {

"insert_time" : [

"[MinKey, MaxKey]"

]

}

}

}

}

},

"rejectedPlans" : [ ]

}

]

}

},

"ok" : 1

}