PostgreSQL 教程: 检查重复索引

一月 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
)

… 然后更改它识别出一些因素,以为您提供最真实的重复索引,而不会遗漏任何可能的重复项。