在Golang和MongoDB中使用$ lookup和$ unwind的请求很慢

Question

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

Answer 1

This is roughly what I'm thinking should help. It's untested since I have no sample data. Plus its Go syntax might be a little wonky since I don't know Go. ;) However, I am relatively sure that the $match statements inside the $lookup pipelines will leverage the available indexes whereas in your query you have the $unwinds before the $matches which effectively render the indexes useless.

pipe := session.DB(shared.DatabaseNamespace).C("events").Pipe([]bson.M{
    {
        "$lookup": bson.M{
            "from": "event_boosts",
            "let": bson.M{ "e_id": "$_id" },
            "pipeline": []bson.M{
                "$match": bson.M{
                    "$expr": bson.M{
                       "$and": []interface{}{
                            bson.M{ "$eq": []string{ "$_event_id", "$$e_id" } },
                            bson.M{ "$eq": []string{ "$is_published", true } }, // Boost is active
                            bson.M{ "$lt": []string{ "$start_date", now } }, // now is between start and end
                            bson.M{ "$gt": []string{ "$end_date", now } },  // now is between start and end
                        },
                    },
                },
            },
            "as": "boost",
        },
    },
    { "$unwind": "$boost" },
    {
        "$lookup": bson.M{
            "from":         "campaigns",
            "let": bson.M{ "c_id": "$boost._campaign_id" },
            "pipeline": []bson.M{
                "$match": bson.M{
                    "$expr": bson.M{
                       "$and": []interface{}{
                            bson.M{ "$eq": []string{ "$id", "$$c_id" } },
                            bson.M{ "$eq": []string{ "$is_published", true } }, // Attached campaign is active
                        },
                    },
                },
            },
            "as": "campaign",
        },
    },
    { "$unwind": "$campaign" },
})