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?

질문됨 9달 전76회 조회
답변 없음

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠