My model : An Event can be boosted by a Campaign that has one or multiple Boost.
I am trying to get all the Event that are boosted with this request below.
// Boosted ...
func (dao *campaignDAO) Boosted() ([]*models.Event, error) {
// Clone the session
session := dao.session.Clone()
defer session.Close()
// Get the time
now := time.Now()
// Create the pipe
pipe := session.DB(shared.DatabaseNamespace).C("events").Pipe([]bson.M{
{
"$lookup": bson.M{
"from": "event_boosts",
"localField": "_id",
"foreignField": "_event_id",
"as": "boost",
},
},
{"$unwind": "$boost"},
{
"$match": bson.M{
"boost.is_published": true, // Boost is active
"boost.start_date": bson.M{"$lt": now}, // now is between start and end
"boost.end_date": bson.M{"$gt": now}, // now is between start and end
},
},
{
"$lookup": bson.M{
"from": "campaigns",
"localField": "boost._campaign_id",
"foreignField": "_id",
"as": "campaign",
},
},
{"$unwind": "$campaign"},
{
"$match": bson.M{
"campaign.is_published": true, // Attached campaign is active
},
},
})
var result []*models.Event
err := pipe.All(&result)
if err != nil {
return nil, err
}
return result, nil
}
But this request takes 3 seconds. Here are the indexes I have in the campaigns :
// NewCampaignDAO returns a new CampaignDAO
func NewCampaignDAO(session *mgo.Session) dao.CampaignDAO {
// Set the collection
col := session.DB(shared.DatabaseNamespace).C("campaigns")
// Set the indexes
col.EnsureIndexKey("start_date")
col.EnsureIndexKey("end_date")
col.EnsureIndexKey("created_by")
col.EnsureIndexKey("is_published")
return &campaignDAO{
session: session,
collection: "campaigns",
}
}
The indexes on the events :
// NewEventDAO returns a new EventDAO
func NewEventDAO(session *mgo.Session) dao.EventDAO {
// Set the collection
col := session.DB(shared.DatabaseNamespace).C("events")
// Set the indexes
col.EnsureIndexKey("old_id")
col.EnsureIndexKey("_parent_id")
col.EnsureIndexKey("_location_id")
col.EnsureIndexKey("price")
col.EnsureIndexKey("name")
col.EnsureIndexKey("category")
col.EnsureIndexKey("start_date")
col.EnsureIndexKey("end_date")
col.EnsureIndexKey("is_recurrent")
col.EnsureIndexKey("is_published")
col.EnsureIndexKey("is_proposed")
col.EnsureIndexKey("tags")
col.EnsureIndexKey("price", "date", "name")
return &eventDAO{
session: session,
collection: "events",
}
}
And the logs of the MongoDB :
2018-06-19T13:22:53.465+0000 I COMMAND [conn506] command clutch.event_boosts command: aggregate { aggregate: "events", pipeline: [ { $lookup: { as: "boost", from: "event_boosts", localField: "_id", foreignField: "_event_id" } }, { $unwind: "$boost" }, { $match: { boost.is_published: true, boost.start_date: { $lt: new Date(1529414570196) }, boost.end_date: { $gt: new Date(1529414570196) } } }, { $lookup: { from: "campaigns", localField: "boost._campaign_id", foreignField: "_id", as: "campaign" } }, { $unwind: "$campaign" }, { $match: { campaign.is_published: true } } ], cursor: {} } planSummary: COLLSCAN keysExamined:0 docsExamined:12936 cursorExhausted:1 numYields:121 nreturned:1 reslen:1149 locks:{ Global: { acquireCount: { r: 52018 } }, Database: { acquireCount: { r: 26009 } }, Collection: { acquireCount: { r: 26008 } } } protocol:op_query 3268ms
I do not get where I could improve.
EDIT : Also, I an wondering if I could improve the request by starting the request on the event_boosts collection, and then lookup in Events.
EDIT2 : Adding the Mongo version.
MongoDB shell version v3.4.6
git version: c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5
OpenSSL version: OpenSSL 1.0.1t 3 May 2016
allocator: tcmalloc
modules: none
build environment:
distmod: debian81
distarch: x86_64
target_arch: x86_64