为什么Postgres不为我的查询使用更好的索引？-Java 学习之路

我有一张表，记录了谁在Twitter类应用程序中关注谁：

\d follow
                               Table "public.follow" .
 Column   |           Type           |                      Modifiers
 ---------+--------------------------+-----------------------------------------------------
xid       | text                     |
followee  | integer                  |
follower  | integer                  |
id        | integer                  | not null default nextval('follow_id_seq'::regclass)
createdAt | timestamp with time zone |
updatedAt | timestamp with time zone |
source    | text                     |
Indexes:
  "follow_pkey" PRIMARY KEY, btree (id)
  "follow_uniq_users" UNIQUE CONSTRAINT, btree (follower, followee)
  "follow_createdat_idx" btree ("createdAt")
  "follow_followee_idx" btree (followee)
  "follow_follower_idx" btree (follower)

表中的条目数超过一百万，当我对查询运行explain analyze时，我得到了这个：

explain analyze SELECT "follow"."follower"
FROM "public"."follow" AS "follow"
WHERE "follow"."followee" = 6
ORDER BY "follow"."createdAt" DESC
LIMIT 15 OFFSET 0;
                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.43..353.69 rows=15 width=12) (actual time=5.456..21.497 
rows=15 loops=1)
->  Index Scan Backward using follow_createdat_idx on follow  (cost=0.43..61585.45 rows=2615 width=12) (actual time=5.455..21.488 rows=15 loops=1)
     Filter: (followee = 6)
     Rows Removed by Filter: 62368
Planning time: 0.068 ms
Execution time: 21.516 ms

为什么它在 follow_createdat_idx 上执行向后索引扫描，如果它使用 follow_followee_idx 它可能会更快执行 .

这个查询在第一次运行时大约需要33毫秒，然后后续调用大约需要22毫秒，我觉得这个问题偏高 .

我正在使用Amazon RDS提供的Postgres 9.5 . 知道这里会发生什么错吗？

1 回答

0
(follower, "createdAt") 上的多列索引user1937198 suggested非常适合查询 - 正如您在测试中找到的那样 .

由于 "createdAt" 可以为NULL（未定义 NOT NULL ），因此您可能需要将 NULLS LAST 添加到查询和索引：
```
...
ORDER BY "follow"."createdAt" DESC NULLS LAST
```
和：
```
"follow_follower_createdat_idx" btree (follower, "createdAt" DESC NULLS LAST)
```
更多：
- PostgreSQL sort by datetime asc, null first?
还有 minor 其他性能影响：
- (follower, "createdAt") 上的多列索引是每行8个字节，大于 (follower) 上的简单索引--44个字节对36个 . 更多（btree索引的页面布局与表格大致相同）：
- Making sense of Postgres row sizes
- 使用HOT更新无法更改以任何方式涉及索引的列 . 向索引添加更多列可能会阻止此优化 - 鉴于列名称，这似乎特别不可能 . 因为你只有 ("createdAt") 的另一个索引，这不是问题 . 更多：
- PostgreSQL Initial Database Size
- 在 ("createdAt") 上有另一个索引没有任何缺点（除了每个的维护成本（写入性能，而不是读取性能） . 两个索引都支持不同的查询 . 您可能需要或者可能不需要索引_1134551_ . 详细说明：
- Is a composite index also good for queries on the first field?
回复于 2024-05-15T17:28:27+08:00

为什么Postgres不为我的查询使用更好的索引？

1 回答

相关问题