sql - 在 Postgresql 中索引外键

Question

像许多 Postgres n00bs 一样，我们有很多带有未索引外键约束的表。在某些情况下，这不会对性能造成很大影响 - 但这需要进一步分析。

我已阅读以下文章:https://www.cybertec-postgresql.com/en/index-your-foreign-key/

并使用以下查询查找所有没有索引的外键:

SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */
   CROSS JOIN LATERAL
      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each key column */
   JOIN pg_catalog.pg_attribute a
      ON a.attnum = x.attnum
         AND a.attrelid = c.conrelid
WHERE NOT EXISTS
        /* is there a matching index for the constraint? */
        (SELECT 1 FROM pg_catalog.pg_index i
         WHERE i.indrelid = c.conrelid
           /* the first index columns must be the same as the
              key columns, but order doesn't matter */
           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
               @> c.conkey::int[])
  AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;

对于具有复合唯一约束的表，这只显示了唯一索引中的“一个”列:

\d topics_items;
-----------------+---------+--------------+---------------+------------------------------
 topics_items_id | integer |              | not null      | generated always as identity
 topic_id        | integer |              | not null      |
 item_id         | integer |              | not null      |
Index:
    "topics_items_pkey" PRIMARY KEY, btree (topics_items_id)
    "topic_id_item_id_unique" UNIQUE CONSTRAINT, btree (topic_id, item_id)
Foreign Keys:
    "topics_items_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE
    "topics_items_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES topics(topic_id) ON DELETE CASCADE

在这种情况下，检查查询仅找到 item_id 而不是 topic_id 作为未索引字段。

公平地说，这只是所用查询的问题，我必须分别为两个字段(topic_id 和 item_id)编制索引 - 或者是否涉及一些黑魔法，只有 item_id 需要一个索引？

Answer 1

tl;dr 您需要在 item_id 上添加索引。 Postgres 索引的“黑魔法”包含在 11. Indexes 中。 .

您在 (topic_id, item_id) 上有一个复合索引，列顺序很重要。 Postgres 可以使用它来索引topic_id 上的查询，topic_id 和item_id 上的查询，但不是(或效率较低)item_id 单独。

来自 11.3. Multicolumn Indexes ...

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns.

-- 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 缩小搜索范围。

---

Postgres 可以 反转索引，如果它认为值得的话。如果有少量可能的主题 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 can also use mulitple indexes if it thinks it will make the query run faster .例如，如果您在 topic_id 上有一个索引，在 item_id 上有一个索引，它可以使用这两个索引并组合结果。例如，其中 topic_id = 23 或 item_id = 42 可以使用 topic_id 索引搜索主题 ID 23，使用 item_id 索引搜索项目 ID 42，然后合并结果。

这通常比复合 (topic_id, item_id) 索引慢。它也可能比使用单个索引慢，所以如果 Postgres 决定不使用多个索引，请不要感到惊讶。

---

一般来说，对于 b-tree 索引，当你有两列时，你有三种可能的组合。

• a+b
• 一个
• b

你需要两个索引。

• (a, b) -- a 和 a + b
• (b) -- b

(a, b) 涵盖了对 a 和 a + b 的搜索。 (b) 包含搜索 b。

当你有三列时，你有七种可能的组合。

• a + b + c
• a+b
• a+c
• 一个
• b+c
• b
• c

但你只需要三个索引。

• (a, b, c) -- a, a + b, a + b + c
• (b, c) -- b, b + c
• (c, a) -- c, c + a

但是，您实际上可能希望避免在三列上建立索引。它通常较慢。你真正想要的是这个。

• (a, b)
• (b, c)
• (c, a)

Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized.

从索引中读取比从表中读取慢。您希望索引减少必须读取的行数，但又不希望 Postgres 不得不进行不必要的索引扫描。

Constraints on columns to the right... are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they'd still have to be scanned through.