|
Hı, good work
I found an bug
The complex consists of the following ways
1.) aggregation framework
2.) date(ISODate) field
3.) createIndex(
{date:-1}
)
4.) run query (if dropIndex or remove sort field everything is ok 
my query:
db.messages.aggregation([{
|
"$match": {
|
"$and": [{
|
"recipient_id": 1607389
|
}, {
|
"deletedbyrecipient": 0
|
}, {
|
"blocked": 0
|
}]
|
}
|
}, {
|
"$sort": {
|
"date": -1/*if indexed very slovly worked , if dropIndex no problem*/
|
}
|
}, {
|
"$group": {
|
"_id": "$sender_id",
|
"id": {
|
"$first": "$_id"
|
},
|
"smileid": {
|
"$first": "$_id"
|
},
|
"blocked": {
|
"$first": "$blocked"
|
},
|
"date": {
|
"$first": "$date"
|
},
|
"deletedbyrecipient": {
|
"$first": "$deletedbyrecipient"
|
},
|
"deletedbysender": {
|
"$first": "$deletedbysender"
|
},
|
"seen": {
|
"$first": "$seen"
|
},
|
"recipient_id": {
|
"$first": "$recipient_id"
|
},
|
"sender_id": {
|
"$first": "$sender_id"
|
},
|
"unseencount": {
|
"$sum": {
|
"$cond": ["$seen", 0, 1]
|
}
|
}
|
}
|
}, {
|
"$sort": {
|
"date": -1
|
}
|
}, {
|
"$limit": 10
|
}, {
|
"$skip": 0
|
}]);
|
|
|
Thanks erdalceylan,
It does look like you are seeing the same issue. I'm going to close this ticket as a duplicate, and encourage you to watch the other ticket for updates on resolving this bug. There is a suggested workaround in the linked ticket. Sorry for the inconvenience!
Charlie
|
|
without index on date output
{
|
"stages" : [
|
{
|
"$cursor" : {
|
"query" : {
|
"$and" : [
|
{
|
"recipient_id" : 1607389
|
},
|
{
|
"deletedbyrecipient" : 0
|
},
|
{
|
"blocked" : 0
|
}
|
]
|
},
|
"fields" : {
|
"blocked" : 1,
|
"date" : 1,
|
"deletedbyrecipient" : 1,
|
"deletedbysender" : 1,
|
"recipient_id" : 1,
|
"seen" : 1,
|
"sender_id" : 1,
|
"_id" : 1
|
},
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "gs.messages",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"$and" : [
|
{
|
"blocked" : {
|
"$eq" : 0
|
}
|
},
|
{
|
"deletedbyrecipient" : {
|
"$eq" : 0
|
}
|
},
|
{
|
"recipient_id" : {
|
"$eq" : 1607389
|
}
|
}
|
]
|
},
|
"winningPlan" : {
|
"stage" : "KEEP_MUTATIONS",
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"blocked" : {
|
"$eq" : 0
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"recipient_id" : 1,
|
"deletedbyrecipient" : 1,
|
"seen" : 1
|
},
|
"indexName" : "recipient_id_1_deletedbyrecipient_1_seen_1",
|
"isMultiKey" : false,
|
"direction" : "forward",
|
"indexBounds" : {
|
"recipient_id" : [
|
"[1607389.0, 1607389.0]"
|
],
|
"deletedbyrecipient" : [
|
"[0.0, 0.0]"
|
],
|
"seen" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
}
|
}
|
},
|
{
|
"$sort" : {
|
"sortKey" : {
|
"date" : -1
|
}
|
}
|
},
|
{
|
"$group" : {
|
"_id" : "$sender_id",
|
"id" : {
|
"$first" : "$_id"
|
},
|
"smileid" : {
|
"$first" : "$_id"
|
},
|
"blocked" : {
|
"$first" : "$blocked"
|
},
|
"date" : {
|
"$first" : "$date"
|
},
|
"deletedbyrecipient" : {
|
"$first" : "$deletedbyrecipient"
|
},
|
"deletedbysender" : {
|
"$first" : "$deletedbysender"
|
},
|
"seen" : {
|
"$first" : "$seen"
|
},
|
"recipient_id" : {
|
"$first" : "$recipient_id"
|
},
|
"sender_id" : {
|
"$first" : "$sender_id"
|
},
|
"unseencount" : {
|
"$sum" : {
|
"$cond" : [
|
"$seen",
|
{
|
"$const" : 0
|
},
|
{
|
"$const" : 1
|
}
|
]
|
}
|
}
|
}
|
},
|
{
|
"$sort" : {
|
"sortKey" : {
|
"date" : -1
|
},
|
"limit" : NumberLong(10)
|
}
|
},
|
{
|
"$skip" : NumberLong(0)
|
}
|
],
|
"ok" : 1
|
}
|
|
|
This is with the index on date? Can you post the explain output without the index on date?
|
|
explain output:
{
|
"stages" : [
|
{
|
"$cursor" : {
|
"query" : {
|
"$and" : [
|
{
|
"recipient_id" : 1607389
|
},
|
{
|
"deletedbyrecipient" : 0
|
},
|
{
|
"blocked" : 0
|
}
|
]
|
},
|
"sort" : {
|
"date" : -1
|
},
|
"fields" : {
|
"blocked" : 1,
|
"date" : 1,
|
"deletedbyrecipient" : 1,
|
"deletedbysender" : 1,
|
"recipient_id" : 1,
|
"seen" : 1,
|
"sender_id" : 1,
|
"_id" : 1
|
},
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "gs.messages",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"$and" : [
|
{
|
"blocked" : {
|
"$eq" : 0
|
}
|
},
|
{
|
"deletedbyrecipient" : {
|
"$eq" : 0
|
}
|
},
|
{
|
"recipient_id" : {
|
"$eq" : 1607389
|
}
|
}
|
]
|
},
|
"winningPlan" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"blocked" : {
|
"$eq" : 0
|
}
|
},
|
{
|
"deletedbyrecipient" : {
|
"$eq" : 0
|
}
|
},
|
{
|
"recipient_id" : {
|
"$eq" : 1607389
|
}
|
}
|
]
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"date" : -1
|
},
|
"indexName" : "date_-1",
|
"isMultiKey" : false,
|
"direction" : "forward",
|
"indexBounds" : {
|
"date" : [
|
"[MaxKey, MinKey]"
|
]
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
}
|
}
|
},
|
{
|
"$group" : {
|
"_id" : "$sender_id",
|
"id" : {
|
"$first" : "$_id"
|
},
|
"smileid" : {
|
"$first" : "$_id"
|
},
|
"blocked" : {
|
"$first" : "$blocked"
|
},
|
"date" : {
|
"$first" : "$date"
|
},
|
"deletedbyrecipient" : {
|
"$first" : "$deletedbyrecipient"
|
},
|
"deletedbysender" : {
|
"$first" : "$deletedbysender"
|
},
|
"seen" : {
|
"$first" : "$seen"
|
},
|
"recipient_id" : {
|
"$first" : "$recipient_id"
|
},
|
"sender_id" : {
|
"$first" : "$sender_id"
|
},
|
"unseencount" : {
|
"$sum" : {
|
"$cond" : [
|
"$seen",
|
{
|
"$const" : 0
|
},
|
{
|
"$const" : 1
|
}
|
]
|
}
|
}
|
}
|
},
|
{
|
"$sort" : {
|
"sortKey" : {
|
"date" : -1
|
},
|
"limit" : NumberLong(10)
|
}
|
},
|
{
|
"$skip" : NumberLong(0)
|
}
|
],
|
"ok" : 1
|
}
|
|
|
Hi erdalceylan,
Thanks for the report. I believe you are experiencing SERVER-7568, where adding the index will cause the aggregation pipeline to use a non-blocking sort, even if it's not faster. To verify, can you run the pipeline with explain both with and without the index, and post the output here?
db.messages.explain().aggregate([ ... ])
|
Thanks!
|
Generated at Thu Feb 08 03:58:24 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.