Index Intersection

I have two partial indexes, that I hope MongoDB will join two of these indexes when all the fields are specified exactly as they are in the partialFilterExpression for both indexes.

I ran an explain plan, but it does not appear to intersect both indexes. Why is that? are there any limitations with index intersection? Does it do both OR and AND conditions?

Referring to https://docs.mongodb.com/manual/core/index-intersection/

Hi @Bluetoba,

Can you please share the index definitions and your query along with the explain plan so we can investigate?

Also, why not create a compound partial index at this point? This would solve your issue, no?

Cheers,
Maxime.

Unfortunately, we have removed these indexes, but we have a collection that has the following fields (for storing chat conversation messages):

ID, Merchant_ID, Buyer_ID, Top_Chain_ID, Top_Chain_FLAG, Last_Msg_Date

Note:

  • A conversation will have one parent message. This parent message has Top_Chain_FLAG=1 and its ID as the Top_Chain_ID.
  • All other chat messages for the same conversation will sthare the same Top_Chain_ID.
  • Some attributes of the last chat message is updated into the top chain record, e.g. Last_Msg_Date.
  • Not all messages have buyer id or merchant id, that’s why we want to set partial index on merchant ids or buyer ids

We had the following indexes
Index 1: Merchant_ID with partialFilterExpression(Merchant_ID exists)
Index 2: Buyer_ID with partialFilterExpression(Buyer_ID exists)
Index 3: Top_Chain_ID, Last_Msg_Date: -1 with partialFilterExpression(Top_Chain__FLAG=1)

What we hoped to do are two scenarios:

  • Search for all messages belonging to a merchant naturally sorted by last_message_date descendingly:
    Merchant_ID = xyz, Top_Chain_FLAG=1
  • Search for all messages belonging to a buyer naturally sorted by last_message_date descendingly:
    Merchant_ID = xyz, Top_Chain_FLAG=1

So, yes we could have two indexes with compound keys, but it would be storage efficient if index intersect works and reuse the same index3. However, it ignores index 3 and simply just use index 1 or index 2 whether it’s merchant or buyer query.

From what I’m reading, I think you need:

  • db.col.createIndex({"Merchant_ID": 1, "Last_Msg_Date": -1}, { partialFilterExpression: { "Merchant_ID": { $exists: true }, "Top_Chain_FLAG": 1 } })
  • db.col.createIndex({"Buyer_ID": 1, "Last_Msg_Date": -1}, { partialFilterExpression: { "Buyer_ID": { $exists: true }, "Top_Chain_FLAG": 1 } })

Note that you don’t need Top_Chain_FLAG in the index. See example here.

This should work correctly with these queries:

  • db.col.find({"Merchant_ID": "John", Top_Chain_FLAG: 1}).sort({"Last_MsgDate": -1})
  • db.col.find({"Buyer_ID": "Bobby", Top_Chain_FLAG: 1}).sort({"Last_MsgDate": -1})

I guess this could even be covered queries if you add the “message” (what you actually need to retrieve here) at the end of the index and project on it. But of course this would make the index bigger and the query would be fully executed in RAM.

Hi @MaBeuLux88_xxx, that is exactly what I wanted to do, i.e. covered queries and what you suggested with two indexes is exactly what ended up having.

However, I wondered why the index interesection did not kick in with the indexex that I had earlier.

thanks,
Gus

I’m not sure why exactly but usually index intersections are ranked pretty low by the query planner because they find that it’s faster (or fast enough) to use only one index.

Also, check out @kevinadi’s answer here.

You could consider using $hint and suggest the 2 indexes but it’s currently not supported - but there is a ticket for this. But as you figured, it’s not really a priority because there is a better alternative in most cases.

Hopefully, you have the performance you were looking for now :slight_smile: !

Cheers,
Maxime.

thank you @MaBeuLux88_xxx

1 Like