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?
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.
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.
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.
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 !