DocumentDB index doesn't work by lookup

0

I'm currently working with DocumentDB 3.6 and have two collections in a database:

users - This collection contains a field called name. users_position - This collection uses userId as a foreign key, which has a unique index. I executed the following query to fetch a user with a specific _id and its corresponding position:

db.getCollection('users').explain("executionStats").aggregate([
    {
        $match: {
            _id: 123
        },
    },
    {
        $lookup: {
            from: "users_position",
            localField: "_id",
            foreignField: "userId",
            as: "userPosition",
        },
    }
]);

The result indicated a collection scan (COLLSCAN) rather than an index scan (IXSCAN) on the foreign table, which was unexpected. Here are the execution stats:

"executionStats": {
    "executionSuccess": true,
    "executionTimeMillis": "913.307",
    "planningTimeMillis": "1.651",
    "executionStages": {
        "stage": "SUBSCAN",
        "nReturned": "1",
        "executionTimeMillisEstimate": "911.598",
        "inputStage": {
            "stage": "HASH_AGGREGATE",
            "nReturned": "1",
            "executionTimeMillisEstimate": "911.529",
            "inputStage": {
                "stage": "HASH_LOOKUP",
                "nReturned": "1",
                "executionTimeMillisEstimate": "911.476",
                "inputStages": [
                    {
                        "stage": "COLLSCAN",
                        "nReturned": "482385",
                        "executionTimeMillisEstimate": "746.718"
                    },
                    {
                        "stage": "HASH",
                        "nReturned": "1",
                        "executionTimeMillisEstimate": "0.128",
                        "inputStage": {
                            "stage": "IXSCAN",
                            "nReturned": "1",
                            "executionTimeMillisEstimate": "0.036",
                            "indexName": "_id_",
                            "direction": "forward"
                        }
                    }
                ]
            }
        }
    }
}

I'm confused about why the foreign table utilized a collection scan instead of an index scan.

  • Hi. Can you post the indexes and stats for the "users_position" collection?

asked 8 months ago75 views
No Answers

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions