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?

gefragt vor 9 Monaten76 Aufrufe
Keine Antworten

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen