MongoDB does not yield a covered query even after including only indexes

[Ruan Chaves]

This is the MongoDB driver I'm using ( Maven dependency ):

    <dependencies>
        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongodb-driver-sync</artifactId>
            <version>3.10.1</version>
        </dependency>
    </dependencies>

I've made two indexes for my collection:  

        this.collection.createIndex(Indexes.ascending(
                    "author",
                    "created_utc",
                    "subreddit"
                ));
        this.collection.createIndex(Indexes.ascending(
                    "created_utc"
                ));

And then I made this query:

        MongoCursor<Document> cursor = this.collection
                .find(and(
                          gte("created_utc", this.lowerBound),
                          lt("created_utc", this.upperBound)))
                .projection(fields(include("author", "created_utc", "subreddit"),
                            exclude("_id"))).iterator();

this.lowerBound and this.upperBound are integers, and this.upperBound > this.lowerBound.

For some unknown reason, MongoDB doesn't seem to be accepting this as a covered query.

When I run the equivalent command directly on mongo and ask for an explanation:

    db.dump.find(
    {created_utc: {$gt: 1543622399, $lt: 1543622401 }},
    {author: 1, created_utc: 1, subreddit: 1, _id: 0})
    .explain("executionStats")

I get a IXSCAN that descends from a FETCH and totalDocsExamined is not zero. So it's not a covered query.

How to make a covered query that will achieve the same results?

Here's how a document on my collection looks like:

    {
        "archived": false,
        "author": "HRNNNGH",
        "author_created_utc": 1533311589,
        "author_flair_background_color": null,
        "author_flair_css_class": null,
        "author_flair_richtext": [],
        "author_flair_template_id": null,
        "author_flair_text": null,
        "author_flair_text_color": null,
        "author_flair_type": "text",
        "author_fullname": "t2_1kcmg9n3",
        "author_patreon_flair": false,
        "body": "I LOVE THIS",
        "can_gild": true,
        "can_mod_post": false,
        "collapsed": false,
        "collapsed_reason": null,
        "controversiality": 0,
        "created_utc": 1543622400,
        "distinguished": null,
        "edited": false,
        "gilded": 0,
        "gildings": {
            "gid_1": 0,
            "gid_2": 0,
            "gid_3": 0
        },
        "id": "eatm38p",
        "is_submitter": false,
        "link_id": "t3_a1vhzf",
        "no_follow": false,
        "parent_id": "t3_a1vhzf",
        "permalink": "/r/ACPocketCamp/comments/a1vhzf/welcome_to_my_hair_salon/eatm38p/",
        "removal_reason": null,
        "retrieved_on": 1546258274,
        "score": 4,
        "send_replies": true,
        "stickied": false,
        "subreddit": "ACPocketCamp",
        "subreddit_id": "t5_3pg1s",
        "subreddit_name_prefixed": "r/ACPocketCamp",
        "subreddit_type": "public"
    }

And here's what I want to achieve with a covered query:

    { "author" : "MyPhantomAccount", "created_utc" : 1543622400, "subreddit" : "ireland" }
    { "author" : "ardentArcane", "created_utc" : 1543622400, "subreddit" : "heroesofthestorm" }
    { "author" : "bbrownabbb", "created_utc" : 1543622400, "subreddit" : "nonononoyes" }
    { "author" : "DANKNESSLEVEL-69", "created_utc" : 1543622400, "subreddit" : "memes" }
    { "author" : "HRNNNGH", "created_utc" : 1543622400, "subreddit" : "ACPocketCamp" }
    { "author" : "DomIstKrieg", "created_utc" : 1543622400, "subreddit" : "AskReddit" }
    { "author" : "befooks", "created_utc" : 1543622400, "subreddit" : "northernlion" }
    { "author" : "meekosmom", "created_utc" : 1543622400, "subreddit" : "raisedbynarcissists" }
    { "author" : "[deleted]", "created_utc" : 1543622400, "subreddit" : "wallstreetbets" }
    { "author" : "Red580", "created_utc" : 1543622400, "subreddit" : "ForwardsFromKlandma" }
    { "author" : "rauland", "created_utc" : 1543622400, "subreddit" : "melbourne" }
    { "author" : "Mr-Morrison94", "created_utc" : 1543622400, "subreddit" : "FortNiteBR" }
    { "author" : "huskiesofinternets", "created_utc" : 1543622400, "subreddit" : "aww" }
    { "author" : "AnimaCorpusLucra", "created_utc" : 1543622400, "subreddit" : "wallstreetbets" }
    { "author" : "Shadow14l", "created_utc" : 1543622400, "subreddit" : "financialindependence" }
    { "author" : "potrap", "created_utc" : 1543622400, "subreddit" : "popheads" }
    { "author" : "FireandBloodandStuff", "created_utc" : 1543622400, "subreddit" : "dankmemes" }
    { "author" : "XChihiro", "created_utc" : 1543622400, "subreddit" : "AskReddit" }
    { "author" : "bjacks12", "created_utc" : 1543622400, "subreddit" : "latterdaysaints" }

[Kevin Adistambha]

Hi,

How to make a covered query that will achieve the same results?

I believe it’s because the index you have is {author: 1, created_utc: 1, subreddit: 1}. However, your query is a range query on created_utc. Due to the way compound index works, the index is arranged as:

• author in ascending order
• created_utc within each author in ascending order
• subreddit within each created_utc within each author in ascending order

In short, a range query on created_utc won’t be able to use the index, since it was not a prefix of the index.

That specific index can only be used for a range query on the author field. For example:

> db.test.explain('executionStats').find({author: {$gte:'HRNNNGH', $lte:'HRNNNGH'}}, {_id:0, author:1, created_utc:1, subreddit:1})
{
  "queryPlanner": {
    "plannerVersion": 1,

....
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 1,
    "executionTimeMillis": 0,
    "totalKeysExamined": 1,
    "totalDocsExamined": 0,

....
        "indexName": "author_1_created_utc_1_subreddit_1",
....

Note above that totalDocsExamined is zero, meaning this is a covered query.

To be able to cover your range query on the created_utc field, you must have an index whose prefix is created_utc. For example:

> db.test.createIndex({created_utc:1, author:1, subreddit:1})

> db.test.explain('executionStats').find({created_utc: {$gt: 1543622399, $lt: 1543622401}}, {_id:0, author:1, created_utc:1, subreddit:1})
{
  "queryPlanner": {
    "plannerVersion": 1,

....
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 1,
    "executionTimeMillis": 0,
    "totalKeysExamined": 1,
    "totalDocsExamined": 0,

....
        "indexName": "created_utc_1_author_1_subreddit_1",
....

Note above that using the new index, totalDocsExamined is zero, meaning this is a covered query.

More information regarding compound index optimization can be found in the blog post Optimizing MongoDB Compound Indexes. Note that although the blog post was written for older MongoDB version, the way compound index works doesn’t change and the post is still relevant today.

Best regards,
Kevin

​