PostgreSQL索引优化问询：带窗口函数的查询及多场景适配

AI 小北

2026-6-23

索引优化分析：带窗口函数的查询性能提升

用户查询语句

SELECT
    id,
    RANK() OVER (
      PARTITION BY user_id
      ORDER BY
        effective_date <= '${date}' DESC,
        effective_date DESC,
        created_at DESC
    ) AS rank
FROM $table WHERE company_id = $companyId AND effective_date <= $date;

用户问题

我想给表添加索引提升该查询性能，猜测(company_id, effective_date)复合索引有用，但还想把user_id加入索引中，核心疑问：

是否应该使用(company_id, effective_date, user_id)单一复合索引？
将user_id放在复合索引末尾对PARTITION BY user_id的性能有帮助吗？
还是应该单独为user_id创建索引？或者这么做完全没用？

补充说明：还有部分查询仅通过company_id和user_id过滤，这类查询的最优索引是(company_id, user_id)，但大部分场景使用上述带窗口函数的查询，优先优化该场景。

创建`(company_id, effective_date, user_id)`索引后的执行计划

WindowAgg  (cost=35.39..36.91 rows=55 width=29) (actual time=0.396..0.596 rows=56 loops=1)
  Output: id, rank() OVER (?), ((effective_date <= '2025-05-30'::date)), effective_date, created_at, user_id
  Buffers: shared hit=5
  ->  Sort  (cost=35.39..35.53 rows=55 width=21) (actual time=0.342..0.352 rows=56 loops=1)
        Output: ((effective_date <= '2025-05-30'::date)), effective_date, created_at, user_id, id
        Sort Key: user_role.user_id, ((user_role.effective_date <= '2025-05-30'::date)) DESC, user_role.effective_date DESC, user_role.created_at DESC
        Sort Method: quicksort  Memory: 29kB
        Buffers: shared hit=5
        ->  Bitmap Heap Scan on public.user_role  (cost=4.84..33.80 rows=55 width=21) (actual time=0.223..0.273 rows=56 loops=1)
              Output: (effective_date <= '2025-05-30'::date), effective_date, created_at, user_id, id
              Recheck Cond: ((user_role.company_id = 47) AND (user_role.effective_date <= '2025-05-30'::date))
              Heap Blocks: exact=3
              Buffers: shared hit=5
              ->  Bitmap Index Scan on user_role__company_id_effective_date_user_id_idx  (cost=0.00..4.83 rows=55 width=0) (actual time=0.170..0.171 rows=56 loops=1)
                    Index Cond: ((user_role.company_id = 47) AND (user_role.effective_date <= '2025-05-30'::date))
                    Buffers: shared hit=2
Settings: jit = 'off'
Planning Time: 0.959 ms
Execution Time: 0.785 ms

分析与优化建议

核心结论

当前(company_id, effective_date, user_id)索引仅能优化查询的过滤阶段，对窗口函数的分区/排序阶段无帮助，调整索引结构可进一步提升性能。

详细分析

现有索引的作用
从执行计划可见，索引确实被用于快速过滤符合company_id和effective_date条件的数据，但过滤后仍需执行全量排序——因为user_id在索引末尾，过滤后的数据中user_id是无序的，数据库无法利用索引有序性满足窗口函数的分区+排序要求，必须重新排序。
user_id放在索引末尾的价值
几乎没有帮助。窗口函数需要先按user_id分区，再按指定规则排序，现有索引顺序为company_id -> effective_date -> user_id，无法让相同user_id的记录聚集，也无法匹配窗口函数的排序规则，仍需额外排序开销。
更优的索引方案
建议创建匹配窗口函数逻辑的覆盖索引，直接消除排序和回表开销：
```
-- PostgreSQL 版本（用INCLUDE避免索引冗余）
CREATE INDEX idx_company_user_effective_created ON user_role 
(company_id, user_id, effective_date DESC, created_at DESC) INCLUDE (id);

-- MySQL 版本（直接将id加入索引末尾）
CREATE INDEX idx_company_user_effective_created ON user_role 
(company_id, user_id, effective_date DESC, created_at DESC, id);
```
理由：
- company_id作为前缀，满足WHERE条件的过滤需求；
- 紧接着user_id，让相同user_id的记录在索引内聚集，直接支持PARTITION BY user_id；
- effective_date DESC, created_at DESC完全匹配窗口函数的ORDER BY规则，而effective_date <= '${date}' DESC的布尔排序需求，可通过effective_date DESC的顺序自然满足（符合条件的记录effective_date更小，在降序排列中会靠前）；
- 包含id列做覆盖索引，避免执行计划中的Bitmap Heap Scan回表操作。
兼顾次要查询的方案
上述优化后的索引前缀为company_id, user_id，已经能覆盖仅按company_id和user_id过滤的次要查询，无需额外创建(company_id, user_id)索引——复合索引的前缀可被复用，额外建索引只会增加表维护成本。

验证方式

创建优化后的索引后，重新执行EXPLAIN (ANALYZE)，若执行计划中不再出现Sort阶段，直接从索引扫描进入WindowAgg，则说明优化生效。

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

PostgreSQL索引优化问询：带窗口函数的查询及多场景适配

用户查询语句 #

用户问题 #

创建(company_id, effective_date, user_id)索引后的执行计划 #

分析与优化建议 #

核心结论 #

详细分析 #

验证方式 #