tl;dr您需要添加索引item_id
。 Postgres 索引的“黑魔法”包含在11. 索引 https://www.postgresql.org/docs/11/indexes.html.
您有一个综合索引(topic_id, item_id)
列顺序很重要。 Postgres 可以使用它来索引查询topic_id
,对两者进行查询topic_id
and item_id
,但不是(或效率较低)item_id
alone.
From 11.3。多列索引 https://www.postgresql.org/docs/11/indexes-multicolumn.html...
多列 B 树索引可与涉及索引列的任何子集的查询条件一起使用,但当前导(最左边)列存在约束时,索引效率最高。
-- indexed
select *
from topics_items
where topic_id = ?
-- also indexed
select *
from topics_items
where topic_id = ?
and item_id = ?
-- probably not indexed
select *
from topics_items
where item_id = ?
这是因为像这样的复合索引(topic_id, item_id)
首先存储主题 ID,然后存储也具有该主题 ID 的项目 ID。为了在此索引中有效地查找项目 ID,Postgres 必须首先使用主题 ID 缩小搜索范围。
Postgrescan如果认为值得付出努力,则反转索引。如果可能的主题 ID 较少,而可能的索引 ID 较多,则会在每个主题 ID 中搜索索引 ID。
例如,假设您有 10 个可能的主题 ID 和 1000 个可能的项目 ID 以及您的索引(topic_id, index_id)
。这就像有 10 个明确标记的主题 ID 桶,每个桶内有 1000 个明确标记的项目 ID 桶。要获取项目 ID 存储桶,它必须查看每个主题 ID 存储桶内部。要使用该索引where item_id = 23
Postgres 必须在 10 个主题 ID 存储桶中的每一个中搜索项目 ID 为 23 的所有存储桶。
但是,如果您有 1000 个可能的主题 ID 和 10 个可能的项目 ID,Postgres 将必须搜索 1000 个主题 ID 存储桶。它很可能会进行全表扫描。在这种情况下,您需要反转索引并使其(item_id, topic_id)
.
这在很大程度上取决于良好的表统计数据,这意味着确保 autovacuum 正常工作。
因此,如果一列的可变性远小于另一列,那么您可以对两列使用单个索引。
如果 Postgres 认为可以使查询运行得更快,它也可以使用多个索引 https://www.postgresql.org/docs/11/indexes-bitmap-scans.html。例如,如果您有一个索引topic_id
和一个索引item_id
, it can使用两个索引并合并结果。例如where topic_id = 23 or item_id = 42
可以使用 topic_id 索引搜索主题 ID 23,使用 item_id 索引搜索项目 ID 42,然后合并结果。
这通常比使用复合材料要慢(topic_id, item_id)
指数。它也可能比使用单个索引慢,因此如果 Postgres 决定不使用多个索引,请不要感到惊讶。
一般来说,对于 B 树索引,当您有两列时,您有三种可能的组合。
并且您需要两个索引。
- (a, b) -- a 和 a + b
- (b) -- b
(a, b)
涵盖 a 和 a + b 的搜索。(b)
涵盖搜索b
.
当您有三列时,您有七种可能的组合。
- a+b+c
- a + b
- a + c
- a
- b + c
- b
- c
但你只需要三个索引。
- (a, b, c) -- a, a + b, a + b + c
- (b, c) -- b, b + c
- (c, a) -- c, c + a
但是,您可能实际上希望避免在三列上建立索引。经常是这样slower。你真正想要的是这个。
应谨慎使用多列索引。在大多数情况下,单列上的索引就足够了,并且节省空间和时间。除非表的使用非常程式化,否则具有超过三列的索引不太可能有帮助。
从索引读取比从表读取慢。您希望索引减少必须读取的行数,但不希望 Postgres 执行不必要的索引扫描。
右侧列的约束...在索引中进行检查,因此它们可以节省对表的访问,但不会减少必须扫描的索引部分。例如,给定 (a, b, c) 上的索引和查询条件 WHERE a = 5 AND b >= 42 AND c = 77 的索引条目将被跳过,但仍然必须扫描它们。