I have an aggregation query where the first operation is $sort. I tried to improve this by indexing the field that was being sorted. I also added the option { allowDiskUse: true }. However, both of those things had no effect at all on the speed, it took the same amount of time.
My pipeline looks like this:
[
{
"$sort": {
"date_created": -1
}
},
{
"$limit": 50
}
]
Any suggestions?
What does explain tell you the query plan is?
"queryPlanner": {
"plannerVersion": 1,
"namespace": "erp-zayntek-prod.quotes",
"indexFilterSet": false,
"parsedQuery": {},
"optimizedPipeline": true,
"winningPlan": {
"stage": "LIMIT",
"limitAmount": 50,
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"date_created": 1,
"number": 1
},
"indexName": "date_created_1_number_1",
"isMultiKey": false,
"multiKeyPaths": {
"date_created": [],
"number": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "backward",
"indexBounds": {
"date_created": [
"[MaxKey, MinKey]"
],
"number": [
"[MaxKey, MinKey]"
]
}
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 50,
"executionTimeMillis": 0,
"totalKeysExamined": 50,
"totalDocsExamined": 50,
"executionStages": {
"stage": "LIMIT",
"nReturned": 50,
"executionTimeMillisEstimate": 0,
"works": 51,
"advanced": 50,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"limitAmount": 50,
"inputStage": {
"stage": "FETCH",
"nReturned": 50,
"executionTimeMillisEstimate": 0,
"works": 50,
"advanced": 50,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"docsExamined": 50,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 50,
"executionTimeMillisEstimate": 0,
"works": 50,
"advanced": 50,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 0,
"keyPattern": {
"date_created": 1,
"number": 1
},
"indexName": "date_created_1_number_1",
"isMultiKey": false,
"multiKeyPaths": {
"date_created": [],
"number": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "backward",
"indexBounds": {
"date_created": [
"[MaxKey, MinKey]"
],
"number": [
"[MaxKey, MinKey]"
]
},
"keysExamined": 50,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
},
"allPlansExecution": []
}
That seems to be hitting the index, whats the server spec, collection size and timings that you are seeing?
Server Spec:
10GB Storage, 2GB RAM, 2 vCPUS (its the M10)
Collection size:
Timing is taking about 2900 ms
Using $project helps some but hopefully there is another solution
That does seem slow, do you have a sample document? Just out of curiosity:
What are you using to run the query
I assume your internet connection is moderately fast
How exactly are you timing the query
I am using the node js mongodb library.
I am starting to wonder about internet connection. I’m not sure how though, it was fast without sort. Today i switched internet connections and it might be faster now.
I am calculating the time delta before and after the query is run.
Sure, sample document below:
{
"_id": {
"$oid": "635e9bf1bfa48f5dbc06cf59"
},
"collection": "quotes",
"client": "zt",
"companyId": "fcf0fae9-ba57-ed11-8c36-000d3a8d9b01",
"date_created": {
"$date": "2022-10-30T17:04:52.251Z"
},
"number": "Q-100166",
"id": "Q-100166",
"headers": {
"currencyCode": "USD",
"salesperson": "",
"externalDocumentNumber": "Q-100166",
"shippingAgentCode": "FEDEX",
"shippingAgentServiceCode": "GRD",
"customerNumber": "STE999",
"email": "",
"phoneNumber": "",
"_customerName": "",
"paymentTermsId": "CREDITCARD",
"_paymentTermsCode": "CREDITCARD",
"sellToAddressLine1": "",
"sellToCity": "",
"sellToState": "",
"sellToCountry": "USA",
"shipToAddressLine1": "",
"shipToCity": "",
"shipToState": "",
"shipToCountry": "USA",
"shipToName": "Test Company"
},
"externalId": "Q-100166",
"lines": [
{
"itemVariantId": "71FA277D-6458-ED11-8C36-000D3A8D9B01",
"description": "Test NZ Item",
"lineObjectNumber": "TNZITEM-1",
"itemId": "48CFCD6F-6458-ED11-8C36-000D3A8D9B01",
"quantity": 1,
"unitCost": 1,
"unitPrice": 2,
"meta": {},
"_gp_profit": 1,
"_gp_margin": 50,
"_gp_ext_profit": 1,
"_gp_ext_margin": 50,
"_ext_cost": 1,
"_ext_price": 2
},
{
"itemVariantId": "15003548-6458-ED11-8C36-000D3A8D9B01",
"description": "Test Serial Item",
"lineObjectNumber": "TSERIALITEM-1",
"itemId": "78177632-6458-ED11-8C36-000D3A8D9B01",
"quantity": 1,
"unitCost": 2,
"unitPrice": 4,
"meta": {},
"_gp_profit": 2,
"_gp_margin": 50,
"_gp_ext_profit": 2,
"_gp_ext_margin": 50,
"_ext_cost": 2,
"_ext_price": 4
}
],
"marketplace": "zt Quote",
"creator": "person",
"flags": [],
"errors": [],
"SO": {
"id": "06126cf2-7458-ed11-8c34-6045bdd449df",
"number": "SO-DF-0020009"
},
"expires": {
"$date": "2022-11-06T16:44:49.609Z"
},
"status": "converted",
"margins": [
{
"label": "Revenue",
"value": 6,
"format": "currency",
"hide": false
},
{
"label": "Cost",
"value": 3,
"format": "currency",
"hide": false
},
{
"label": "Burden",
"value": "5.5%",
"format": "number",
"hide": false
},
{
"label": "Profit",
"value": 3,
"format": "currency",
"hide": false,
"vclass": ""
},
{
"label": "GP Percent",
"value": 0.445,
"format": "gp",
"hide": false,
"vclass": ""
}
],
"expired": false,
"complete": false,
"deleteAfterSuccess": true,
"flagged_by_processor": true,
"openFlag": true
}
To eliminate connectivity why not try piping the output of the query to a new collection and see how long that takes?
Just add an $out stage to the aggregation query and see how long that takes…you have a slight overhead of doing a write, but it could help eliminate a possible issue.
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.