Cosmos DB查询优化：先按分区键过滤再遍历数组

原问题 #

现有一个可正常运行但速度慢、成本高的Cosmos DB查询：

SELECT c.actionType as actionType, count(1) as count 
FROM c in t.processList
WHERE c.processTimestamp > @from
GROUP BY c.actionType

需求是通过父文档的分区键minute先过滤（比如minute > x），再遍历processList数组，以此省去子元素的c.processTimestamp > @from判断，优化查询性能。

文档结构示例 #

{
    "id": "b6fd10cc-3a0b-4666-bf55-f22436a5f8d9",
    "Name": "xxx",
    "Age": 1,
    "minute": 202302021026,
    "processList": [
        {
            "processTimestamp": "2023-02-01T10:28:48.3004825Z",
            "actionType": "Action1",
            "oldValue": "2/1/2023 10:28:41 AM",
            "newValue": "2/1/2023 10:28:48 AM"
        },
        {
            "processTimestamp": "2023-02-01T10:28:48.3004825Z",
            "actionType": "Action2",
            "oldValue": "2/1/2023 10:28:48 AM",
            "newValue": "2/1/2023 10:28:48 AM"
        }
    ]
}

错误尝试的查询 #

曾尝试子查询但未成功，错误语句如下：

SELECT c.actionType as actionType, count(1) as count 
FROM (SELECT * FROM C WHERE c.minute > 9) in t.processList
WHERE c.processTimestamp > @from
GROUP BY c.actionType

优化后的正确查询 #

SELECT p.actionType as actionType, COUNT(1) as count
FROM c
WHERE c.minute > @targetMinute -- 先通过分区键过滤父文档，减少扫描范围
JOIN p IN c.processList
GROUP BY p.actionType

优化说明 #

1. 优先用分区键minute过滤父文档，Cosmos DB会利用分区索引快速定位目标文档，避免全集合扫描，大幅降低RU消耗、提升查询速度
2. 过滤后再通过JOIN展开processList数组，此时父文档的时间范围已满足需求，无需再判断子元素的processTimestamp

期望结果 #

[
    {
        "actionType": "action1",
        "count": 85351
    },
    {
        "actionType": "action2",
        "count": 2354
    }
]

内容的提问来源于stack exchange，提问作者Pieter