0

please see below query which is supposed to use compound_index but MongoDB opt in to use id index which is a default index.

db.getCollection('tmp_properties').find(
    {
       mls:"mls_name", 
       $or : [
            {is_custom_listing : false},
            {is_custom_listing : {$exists : false}}
        ],
        listing_status : {$in : ['Sold', 'Rented']}
    },
    {
        id : 1
    }
).limit(1000).sort({_id:-1}).explain();

Here is my index:

compound_index
{
    "mls" : 1.0,
    "is_custom_listing" : 1.0,
    "listing_status" : 1.0,
    "id" : 1.0,
    "_id" : 1.0
}

Explain

/* 1 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "tmp_properties",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "$or" : [ 
                        {
                            "is_custom_listing" : {
                                "$eq" : false
                            }
                        }, 
                        {
                            "$nor" : [ 
                                {
                                    "is_custom_listing" : {
                                        "$exists" : true
                                    }
                                }
                            ]
                        }
                    ]
                }, 
                {
                    "mls" : {
                        "$eq" : "crmls"
                    }
                }, 
                {
                    "listing_status" : {
                        "$in" : [ 
                            "Rented", 
                            "Sold"
                        ]
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 1000,
            "inputStage" : {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "id" : 1.0
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "$or" : [ 
                                    {
                                        "is_custom_listing" : {
                                            "$eq" : false
                                        }
                                    }, 
                                    {
                                        "$nor" : [ 
                                            {
                                                "is_custom_listing" : {
                                                    "$exists" : true
                                                }
                                            }
                                        ]
                                    }
                                ]
                            }, 
                            {
                                "mls" : {
                                    "$eq" : "crmls"
                                }
                            }, 
                            {
                                "listing_status" : {
                                    "$in" : [ 
                                        "Rented", 
                                        "Sold"
                                    ]
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "_id" : 1
                        },
                        "indexName" : "_id_",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "_id" : []
                        },
                        "isUnique" : true,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "backward",
                        "indexBounds" : {
                            "_id" : [ 
                                "[MaxKey, MinKey]"
                            ]
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "id" : 1.0
                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "_id" : -1.0
                    },
                    "limitAmount" : 1000,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "inputStage" : {
                                "stage" : "OR",
                                "inputStages" : [ 
                                    {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "mls" : 1.0,
                                            "is_custom_listing" : 1.0,
                                            "listing_status" : 1.0,
                                            "id" : 1.0,
                                            "_id" : 1.0
                                        },
                                        "indexName" : "compound_index",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "mls" : [],
                                            "is_custom_listing" : [],
                                            "listing_status" : [],
                                            "id" : [],
                                            "_id" : []
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mls" : [ 
                                                "[\"crmls\", \"crmls\"]"
                                            ],
                                            "is_custom_listing" : [ 
                                                "[false, false]"
                                            ],
                                            "listing_status" : [ 
                                                "[\"Rented\", \"Rented\"]", 
                                                "[\"Sold\", \"Sold\"]"
                                            ],
                                            "id" : [ 
                                                "[MinKey, MaxKey]"
                                            ],
                                            "_id" : [ 
                                                "[MinKey, MaxKey]"
                                            ]
                                        }
                                    }, 
                                    {
                                        "stage" : "FETCH",
                                        "filter" : {
                                            "$nor" : [ 
                                                {
                                                    "is_custom_listing" : {
                                                        "$exists" : true
                                                    }
                                                }
                                            ]
                                        },
                                        "inputStage" : {
                                            "stage" : "IXSCAN",
                                            "keyPattern" : {
                                                "mls" : 1.0,
                                                "is_custom_listing" : 1.0,
                                                "listing_status" : 1.0,
                                                "id" : 1.0,
                                                "_id" : 1.0
                                            },
                                            "indexName" : "compound_index",
                                            "isMultiKey" : false,
                                            "multiKeyPaths" : {
                                                "mls" : [],
                                                "is_custom_listing" : [],
                                                "listing_status" : [],
                                                "id" : [],
                                                "_id" : []
                                            },
                                            "isUnique" : false,
                                            "isSparse" : false,
                                            "isPartial" : false,
                                            "indexVersion" : 2,
                                            "direction" : "forward",
                                            "indexBounds" : {
                                                "mls" : [ 
                                                    "[\"crmls\", \"crmls\"]"
                                                ],
                                                "is_custom_listing" : [ 
                                                    "[null, null]"
                                                ],
                                                "listing_status" : [ 
                                                    "[\"Rented\", \"Rented\"]", 
                                                    "[\"Sold\", \"Sold\"]"
                                                ],
                                                "id" : [ 
                                                    "[MinKey, MaxKey]"
                                                ],
                                                "_id" : [ 
                                                    "[MinKey, MaxKey]"
                                                ]
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            }, 
            {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "id" : 1.0
                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "_id" : -1.0
                    },
                    "limitAmount" : 1000,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "filter" : {
                                "$or" : [ 
                                    {
                                        "is_custom_listing" : {
                                            "$eq" : false
                                        }
                                    }, 
                                    {
                                        "$nor" : [ 
                                            {
                                                "is_custom_listing" : {
                                                    "$exists" : true
                                                }
                                            }
                                        ]
                                    }
                                ]
                            },
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "mls" : 1.0,
                                    "is_custom_listing" : 1.0,
                                    "listing_status" : 1.0,
                                    "id" : 1.0,
                                    "_id" : 1.0
                                },
                                "indexName" : "compound_index",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "mls" : [],
                                    "is_custom_listing" : [],
                                    "listing_status" : [],
                                    "id" : [],
                                    "_id" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "mls" : [ 
                                        "[\"crmls\", \"crmls\"]"
                                    ],
                                    "is_custom_listing" : [ 
                                        "[MinKey, MaxKey]"
                                    ],
                                    "listing_status" : [ 
                                        "[\"Rented\", \"Rented\"]", 
                                        "[\"Sold\", \"Sold\"]"
                                    ],
                                    "id" : [ 
                                        "[MinKey, MaxKey]"
                                    ],
                                    "_id" : [ 
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        ]
    }
    "ok" : 1.0
}

UPDATE

After I change index order to:

{
    "mls" : 1,
    "is_custom_listing" : 1,
    "listing_status" : 1,
    "_id" : 1,
    "id" : 1
}

I got following explain output:

/* 1 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "tmp_properties",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "$or" : [ 
                        {
                            "is_custom_listing" : {
                                "$eq" : false
                            }
                        }, 
                        {
                            "$nor" : [ 
                                {
                                    "is_custom_listing" : {
                                        "$exists" : true
                                    }
                                }
                            ]
                        }
                    ]
                }, 
                {
                    "mls" : {
                        "$eq" : "crmls"
                    }
                }, 
                {
                    "listing_status" : {
                        "$in" : [ 
                            "Rented", 
                            "Sold"
                        ]
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 1000,
            "inputStage" : {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "id" : 1.0
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "$or" : [ 
                                    {
                                        "is_custom_listing" : {
                                            "$eq" : false
                                        }
                                    }, 
                                    {
                                        "$nor" : [ 
                                            {
                                                "is_custom_listing" : {
                                                    "$exists" : true
                                                }
                                            }
                                        ]
                                    }
                                ]
                            }, 
                            {
                                "mls" : {
                                    "$eq" : "crmls"
                                }
                            }, 
                            {
                                "listing_status" : {
                                    "$in" : [ 
                                        "Rented", 
                                        "Sold"
                                    ]
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "_id" : 1
                        },
                        "indexName" : "_id_",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "_id" : []
                        },
                        "isUnique" : true,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "_id" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "id" : 1.0
                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "_id" : 1.0
                    },
                    "limitAmount" : 1000,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "inputStage" : {
                                "stage" : "OR",
                                "inputStages" : [ 
                                    {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "mls" : 1.0,
                                            "is_custom_listing" : 1.0,
                                            "listing_status" : 1.0,
                                            "_id" : 1.0,
                                            "id" : 1.0
                                        },
                                        "indexName" : "compound_index",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "mls" : [],
                                            "is_custom_listing" : [],
                                            "listing_status" : [],
                                            "_id" : [],
                                            "id" : []
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mls" : [ 
                                                "[\"crmls\", \"crmls\"]"
                                            ],
                                            "is_custom_listing" : [ 
                                                "[false, false]"
                                            ],
                                            ....
                                        }
                                    }, 
                                    {
                                        "stage" : "FETCH",
                                        "filter" : {
                                            "$nor" : [ 
                                                {
                                                    "is_custom_listing" : {
                                                        "$exists" : true
                                                    }
                                                }
                                            ]
                                        },
                                        "inputStage" : {
                                            "stage" : "IXSCAN",
                                            "keyPattern" : {
                                                "mls" : 1.0,
                                                "is_custom_listing" : 1.0,
                                                "listing_status" : 1.0,
                                                "_id" : 1.0,
                                                "id" : 1.0
                                            },
                                            "indexName" : "compound_index",
                                            "isMultiKey" : false,
                                            "multiKeyPaths" : {
                                                "mls" : [],
                                                "is_custom_listing" : [],
                                                "listing_status" : [],
                                                "_id" : [],
                                                "id" : []
                                            },
                                            "isUnique" : false,
                                            "isSparse" : false,
                                            "isPartial" : false,
                                            "indexVersion" : 2,
                                            "direction" : "forward",
                                            "indexBounds" : {
                                                "mls" : [ 
                                                    "[\"crmls\", \"crmls\"]"
                                                ],
                                                ....
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            }, 
            {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "id" : 1.0
                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "_id" : 1.0
                    },
                    "limitAmount" : 1000,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "filter" : {
                                "$or" : [ 
                                    {
                                        "is_custom_listing" : {
                                            "$eq" : false
                                        }
                                    }, 
                                    {
                                        "$nor" : [ 
                                            {
                                                "is_custom_listing" : {
                                                    "$exists" : true
                                                }
                                            }
                                        ]
                                    }
                                ]
                            },
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "mls" : 1.0,
                                    "is_custom_listing" : 1.0,
                                    "listing_status" : 1.0,
                                    "_id" : 1.0,
                                    "id" : 1.0
                                },
                                "indexName" : "compound_index",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "mls" : [],
                                    "is_custom_listing" : [],
                                    "listing_status" : [],
                                    "_id" : [],
                                    "id" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "mls" : [ 
                                        "[\"crmls\", \"crmls\"]"
                                    ],
                                  ....
                                }
                            }
                        }
                    }
                }
            }
        ]
    }
}
Improve this question

1 Answer 1

Reset to default
1

Notice that the rejected plan using the compound index required an in-memory sort. If you run the explain with the "allPlansExecution" option, you can compare the performance of each.

The main difference between the considered index is the index on {_id:1} supports sorting the results without an in-memory sort stage.

The compound index lists the equality matched fields first, then the unfiltered id field, and finally the sort field.

If you rebuild that index to follow the ESR rule, this query might both avoid the in-memory sort, and be fully covered by the index. i.e.:

{
    "mls" : 1,
    "_id" : 1,
    "is_custom_listing" : 1,
    "listing_status" : 1,
    "id" : 1
}
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

thank you for the answer. I tried your suggestion but it didn't work. I updated question with new explain result. I had to omit few fields because stackoverflow has max 3000 character limit.
My apologies, I copied the fields into the wrong order - that index spec doesn't follow ESR, edited to correct that.
Thank you so much. I spent days on this but couldn't figure this out. I wonder how you decided index order by looking at the execution plan? I would love to learn more. P.s. happy to buy you a coffee for helping me. Thanks once again
If the query plan includes a sort stage or sort key generator, that will be a blocking stage, and it is going to be extremely expensive with large result sets. The query executor can only use an index to eliminate the sort if it can scan the index in such a way that it finds the result in pre-sorted order. The most efficient queries are able to use the index for both sorting and document selection.
The sort in your query was on _id, so placing that field after any equality matched fields, but before the range match, partial ($or) match, and projected fields allows the query executor to scan the index, and retrieve the document already in the final order, so no need for an in memory sort.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.