Mongodb query execution take too much time..

[Swati Sharma]

Hello Everyone,

I am working on a golang project and I am using mongodb to store my data. Mongodb query took 3 sec to execute. I have a collection named "bookings" with around 19,000 documents and each document containing around 200 fields (4.385KB). I have written a aggregate query to get my result.

Mongodb log for the query is as below:

{ 

aggregate: "bookings", 

pipeline: [ 

{ $lookup: 

{ 

localField: "uid", 

from: "users", 

foreignField: "_id", 

as: "customer_info" 

} 

}, 

{ $unwind: "$customer_info" }, 

{ $lookup: 

{ 

foreignField: "_id", 

as: "provider_info", 

localField: "provider_ids", 

from: "users" 

} 

}, 

{ $match: 

{ status: { $in: [ 0, 6, 7, 8 ] }, 

end_date_timestamp: { $gte: 1539839212 }, 

is_visible: true, 

customer_info.status: { $ne: 9 }, 

provider_info.status: { $ne: 9 } } 

}, 

{ $lookup: 

{ 

localField: "address_id", 

from: "user_addresses", 

foreignField: "_id", 

as: "address" 

} 

}, { $unwind: "$address" }, 

{ $sort: { arrival_date_time: 1 } }, 

{ $skip: 0 }, 

{ $limit: 20 }, 

{ $project: 

{ 

reminder_before_day_hour_time: 0, 

customer_info.apt_no: 0, 

customer_info.invitation_sent: 0, 

provider_info.password: 0, 

} 

} 

], 

cursor: {}, 

$readPreference: 

{ mode: "secondaryPreferred" }, 

$db: "ironetwork_bk_db" } 

planSummary: IXSCAN 

{ 

status: 1, 

end_date_timestamp: 1, 

is_visible: 1, 

arrival_date_time: 1 

} 

keysExamined:16281 

docsExamined:16277 

hasSortStage:1 

cursorExhausted:1 

numYields:183 

nreturned:20 

reslen:102932 

locks:{ 

Global: 

{ acquireCount: { r: 97909 } }, 

Database: 

{ acquireCount: { r: 97909 } }, 

Collection: 

{ acquireCount: { r: 97908 } } 

} 

protocol:op_query 3352ms

Can someone guide we how can I reduce the execution time for this query,

[Sandeep Singh]

Try moving your match stage in the front with supporting indexes. Right now your query is scanning almost the entire collection because your match stage is somewhere in the middle.

--
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...@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/3e036f4f-5e1e-4693-89f4-a3ad013fc1bb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Swati Sharma]

Hello Sandeep.
Thank you for your reply.
Actually my match stage depend on the two  lookup stages that's why it is in the middle. I know we have to place the match stage at the top to write a optimize query. 

[Kevin Adistambha]

Hi Swati,

Further to Sandeep’s answer, here’s the stats of the query you have:

    keysExamined:16281 
    docsExamined:16277 
    hasSortStage:1 
    cursorExhausted:1 
    numYields:183 
    nreturned:20

This shows that MongoDB examined more than 16,000 documents, only to return 20 of them (due to the $limit). If this is a query that you expect to run very often, it’s probably best to rethink your schema design so that this could be made more efficient. Some resources on this topic:

• Data Model Examples and Patterns
• 6 Rules of Thumb for MongoDB Schema Design: Part 1

Another point is to not run mongod alongside another resource-intensive processes (e.g. another mongod, another database server, etc.) since it will also have to compete for the machine’s resources. It’s usually best to run MongoDB on its own hardware to get maximum performance.

Best regards,
Kevin

​

[Swati Sharma]

Hello Kevin,

Thank you for your reply.

Best Regards,

Swati Sharma