发布于 29天前

在 Postgres（或任何其他 SQL DB）中建立索引的最佳方法是什么

我有以下查询：

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）上使用单个索引吗？将 userId 作为组合索引的最后一部分是否有助于性能，或者我应该只在 UserID 上添加单独的索引？或者这并不重要，因为它根本无济于事？PARTITION BY user_id

另外值得一提的是：还有一些查询不使用 ranks 和 partition，并且 where 过滤器仅适用于 company_id 和 user_id。在这种情况下，最佳索引选项是（company_id， user_id）。但是，大多数情况下会使用第一个查询，因此提高该查询的性能将是最重要的目标。

while 在（company_id， effective_date， user_id）上具有索引的输出：EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)

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

klustron

浏览 (23)