Okay the use case is for chat collection. The first chat document has a flag, Top_Chain_FLG = 1. All documents belonging to the same conversation, share the same foreign key, Top_Chain_ID, that point to the ID of first chat document.
The conversation is between merchants and buyers, as identified by Merchant_ID and Buyer_ID. So these IDs are part of the document.
The first chat document, also known as the Top_Chain documents are augmented with the attributes of the last chat message, in order for us to save many queries to extract information about the first and last chat message. It also includes information such as Last_Message_Date.
So, it essential fields look like the following:
ID, Top_Chain_ID, Top_Chain_FLG, Merchant_ID, Buyer_ID, Last_Message_Date, etc, etc (e.g. message itself, createdAt, createdDate)
What we need is to display a list of chat messages for a merchant or a buyer in the order of last messages received (known as Last_Message_Date descendingly). So, instead of doing a separate order by, I wanted to rely on the index to sort it.
So, what I initially had in mine is to create three partial indexes.
- Merchant_ID (partialFilterExpression where id exists)
- Buyer_ID (partialFilterExpression where id exists)
- Top_Chain_ID, Last_Message_Date descending (partialFilterExpression where Top_Chain_FLG=1). Note: the reason I included Last_Message_Date descending is because I don’t want to be sorting manually, and I want to use what’s already in the index table.
I also thought that Index Intersection will take place on both index#1 and #3 and when say I want to query on the following, but the explain plan didn’t indicate that.
Merchant_ID = xyx
Question 1: Why doesn’t index intersection take place?
So, I created one index that combines content of index #1 and #3 together.
Merchant_ID, Top_Chain_ID, Last_Message_Date descending (partialFilterExpression where Merchant_ID exists, Top_Chain_FLG=1)
Question 2: The explain plain works as intended, but I still don’t know if the sorting really use the Last_Message_Date from the index? Does it?
Question 3: Should I use a hint? how can I do a hint based an index name?