一月 30, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中检查重复索引。
目录
为什么要删除重复的索引？
每个人都知道数据库索引是一件好事，因为它可以加快 SQL 查询的速度。但这是有代价的。
索引的缺点有：
- 索引会占用空间。 数据库索引使用的存储空间与数据本身一样多的情况，并不少见。而且，您数据库想要的可靠、快速的存储设备，并不一定便宜。索引占用的空间，也会增加物理备份的大小和持续时间。
- 索引会减慢数据修改速度。 每当您
INSERT到表或从表DELETE时，除了表本身（“堆表”）之外，还必须修改所有索引。而且修改索引的复杂数据结构，比修改堆表本身的成本高得多，堆表之所以得名，正是因为它基本上是一堆无序的数据（众所周知，维护秩序比无序管理需要做更多工作）。修改一个带索引的表的成本，很容易比修改一个无索引的表高出一个数量级。 - 索引会阻碍 HOT 更新。 由于 PostgreSQL 的机制原因，每次
UPDATE都会导致写入一个新的行版本（“元组”），这会导致表上的每个索引中都有一个新条目。这种行为被称为“写入放大”，并引起了很多抨击。如果 a）新元组与旧元组位于同一表块中，并且 b）未修改索引列，则可以避免这种不良影响。然后，PostgreSQL 将新元组创建为“仅堆表元组”（即 HOT），这样效率更高，也减少了VACUUM必须做的工作。
查找确切重复的索引
查找具有相同列集、相同操作符类、表达式和谓词的多个索引，这些表示它们是等效的。通常省去其中一个是安全的，但是不保证这样。
SELECT ni.nspname || '.' || ct.relname AS "table",
ci.relname AS "dup index",
pg_get_indexdef(i.indexrelid) AS "dup index definition",
i.indkey AS "dup index attributes",
cii.relname AS "encompassing index",
pg_get_indexdef(ii.indexrelid) AS "encompassing index definition",
ii.indkey AS "enc index attributes"
FROM pg_index i
JOIN pg_class ct ON i.indrelid=ct.oid
JOIN pg_class ci ON i.indexrelid=ci.oid
JOIN pg_namespace ni ON ci.relnamespace=ni.oid
JOIN pg_index ii ON ii.indrelid=i.indrelid AND
ii.indexrelid != i.indexrelid AND
(array_to_string(ii.indkey, ' ') || ' ') like (array_to_string(i.indkey, ' ') || ' %') AND
(array_to_string(ii.indcollation, ' ') || ' ') like (array_to_string(i.indcollation, ' ') || ' %') AND
(array_to_string(ii.indclass, ' ') || ' ') like (array_to_string(i.indclass, ' ') || ' %') AND
(array_to_string(ii.indoption, ' ') || ' ') like (array_to_string(i.indoption, ' ') || ' %') AND
NOT (ii.indkey::integer[] @> ARRAY[0]) AND -- Remove if you want expression indexes (you probably don't)
NOT (i.indkey::integer[] @> ARRAY[0]) AND -- Remove if you want expression indexes (you probably don't)
i.indpred IS NULL AND -- Remove if you want indexes with predicates
ii.indpred IS NULL AND -- Remove if you want indexes with predicates
CASE WHEN i.indisunique THEN ii.indisunique AND
array_to_string(ii.indkey, ' ') = array_to_string(i.indkey, ' ') ELSE true END
JOIN pg_class ctii ON ii.indrelid=ctii.oid
JOIN pg_class cii ON ii.indexrelid=cii.oid
WHERE ct.relname NOT LIKE 'pg_%' AND
NOT i.indisprimary
ORDER BY 1, 2, 3;
所以，你可能注意到我们仍然会得到一些误报，对吗？这是因为索引可以具有所有相同的列，但仍然是不同的索引。例如，它可以使用 varchar_pattern_ops、GiST，或是一个部分索引。但是，我们希望看到这些索引，因为它们通常在功能上与其他索引重复，即使它们并不完全相同。例如，您可能不需要同时基于( status WHERE cancelled is null )和( status )建立索引。
查找部分匹配的索引
如果索引包含另一个索引的所有列，以及更多列，那该怎么办？就像你在(id, name)上有一个索引一样，你可能不需要在(id)上再有另一个索引。好吧，下面是一个查找部分匹配索引的查询。
-- check for containment
-- i.e. index A contains index B
-- and both share the same first column
-- but they are NOT identical
WITH index_cols_ord as (
SELECT attrelid, attnum, attname
FROM pg_attribute
JOIN pg_index ON indexrelid = attrelid
WHERE indkey[0] > 0
ORDER BY attrelid, attnum
),
index_col_list AS (
SELECT attrelid,
array_agg(attname) as cols
FROM index_cols_ord
GROUP BY attrelid
),
dup_natts AS (
SELECT indrelid, indexrelid
FROM pg_index as ind
WHERE EXISTS ( SELECT 1
FROM pg_index as ind2
WHERE ind.indrelid = ind2.indrelid
AND ( ind.indkey @> ind2.indkey
OR ind.indkey <@ ind2.indkey )
AND ind.indkey[0] = ind2.indkey[0]
AND ind.indkey <> ind2.indkey
AND ind.indexrelid <> ind2.indexrelid
) )
SELECT userdex.schemaname as schema_name,
userdex.relname as table_name,
userdex.indexrelname as index_name,
array_to_string(cols, ', ') as index_cols,
indexdef,
idx_scan as index_scans
FROM pg_stat_user_indexes as userdex
JOIN index_col_list ON index_col_list.attrelid = userdex.indexrelid
JOIN dup_natts ON userdex.indexrelid = dup_natts.indexrelid
JOIN pg_indexes ON userdex.schemaname = pg_indexes.schemaname
AND userdex.indexrelname = pg_indexes.indexname
ORDER BY userdex.schemaname, userdex.relname, cols, userdex.indexrelname;
上面的查询查找索引，其中一个索引包含与第二个索引相同的所有列，以及更多列，并且它们都有相同的第一列。虽然这些索引中的许多实际上可能不是重复的，但其中很多都是重复的。
显然，您可以对此查询修改出其他版本，例如以不同的顺序搜索具有相同列的所有多列索引，或者前两列相同但其他列不同的索引。要修改出自己的版本，关键是编辑此子句中包含的筛选条件：
WHERE EXISTS ( SELECT 1
FROM pg_index as ind2
WHERE ind.indrelid = ind2.indrelid
AND ( ind.indkey @> ind2.indkey
OR ind.indkey <@ ind2.indkey )
AND ind.indkey[0] = ind2.indkey[0]
AND ind.indkey <> ind2.indkey
AND ind.indexrelid <> ind2.indexrelid
)
… 然后更改它识别出一些因素，以为您提供最真实的重复索引，而不会遗漏任何可能的重复项。