用于PostgreSQL索引维护的有用查询

PostgreSQL 拥有丰富的索引功能，并且有很多文章解释索引的语法、用法和价值。在本文中，我将编写基本且有用的查询来查看数据库索引的状态。人们开发数据库一段时间后，当需要对软件架构进行更改时，他们忘记了以前的索引清理。这种方法会造成混乱，有时还会因为索引太多而降低数据库速度。每当我们进行更新或插入时，索引都会与实际表一起更新，因此需要进行清理。

有一个 wiki 页面有一些与PostgreSQL 索引维护相关的查询。

在编写查询之前，我想介绍一个目录表 pg_index。该表包含有关索引的信息。这是基本的目录表，所有基于索引的视图都使用同一个表。

1. 有时您需要查看表有多少个索引。此查询将显示模式限定的表名称及其索引名称。

   SELECT CONCAT(n.nspname,'.', c.relname) AS table,
       i.relname AS index_name FROM pg_class c
        JOIN pg_index x ON c.oid = x.indrelid
        JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'pgbench_accounts';
             table          | index_name       
   -------------------------+------------------------
    public.pgbench_accounts | pgbench_accounts_pkey
    public.pgbench_accounts | pgbench_accounts_index
   (2 rows)
   

2. 众所周知，索引是一种性能特征，但除此之外，它还用于确保唯一性。但为了确保唯一性，我们需要一种单独类型的索引，称为唯一索引。为了检查索引是否唯一，pg_index 有一个名为“indisunique”的列来标识索引的唯一性。

   SELECT    i.relname AS index_name,
             indisunique is_unique
   FROM      pg_class c
   JOIN      pg_index x ON c.oid = x.indrelid
   JOIN      pg_class i ON i.oid = x.indexrelid
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE     c.relkind = ANY (ARRAY['r', 't'])
   AND       c.relname LIKE 'pgbench_accounts';
          index_name       | is_unique 
   ------------------------+-----------
    pgbench_accounts_pkey  | t
    pgbench_accounts_index | f
   (2 rows)
   

3. 有一个非常简单的方法来获取 PostgreSQL 索引的大小。这是一个列出 PostgreSQL 大小的查询。

   SELECT pg_size_pretty(pg_relation_size('pgbench_accounts_index'));
    pg_size_pretty 
   ----------------
    132 MB
   (1 row)
   

4. 这是索引列表，其中包含表总大小和索引大小，这对于将表大小与其相应索引进行比较非常有用。了解表的大小、索引以及表的总大小非常有用。

   SELECT    CONCAT(n.nspname,'.', c.relname) AS table,
             i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size,
             pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size,
             pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c 
   JOIN      pg_index x ON c.oid = x.indrelid
   JOIN      pg_class i ON i.oid = x.indexrelid
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE     c.relkind = ANY (ARRAY['r', 't'])
   AND       n.oid NOT IN (99, 11, 12375);
             table          | index_name       | table_size | index_size | total_size 
   -------------------------+------------------------+------------+------------+------------
    public.pgbench_tellers  | pgbench_tellers_pkey   | 88 kB      | 64 kB  | 152 kB
    public.pgbench_accounts | pgbench_accounts_pkey  | 2561 MB    | 428 MB | 3122 MB
    public.pgbench_accounts | pgbench_accounts_index | 2561 MB    | 132 MB | 3122 MB
    public.pgbench_branches | pgbench_branches_pkey  | 8192 bytes | 16 kB  | 24 kB
   (4 rows)
   

   pg_relation_size：函数给出关系的大小。它用于获取表/索引的大小。

   pg_total_relation_size：这是一个特殊函数，给出表及其所有索引的总大小。

5. 获取索引的查询。该查询将显示索引创建查询。

   SELECT pg_get_indexdef(indexrelid) AS index_query
   FROM   pg_index WHERE  indrelid = 'pgbench_accounts'::regclass;
                                        index_query
   ----------------------------------------------------------------------------------------
   CREATE UNIQUE INDEX pgbench_accounts_pkey ON public.pgbench_accounts USING btree (aid)
   CREATE INDEX pgbench_accounts_index ON public.pgbench_accounts USING btree (bid)
   CREATE INDEX pgbench_accounts_index_dup ON public.pgbench_accounts USING btree (bid)
   (3 rows)
   

6. 如果您的索引损坏或膨胀，您需要重新构建该索引。同时，您不想阻止表上的操作，因此您可以选择 REINDEX CONCURRENTLY 命令。

   REINDEX INDEX CONCURRENTLY idx;
   REINDEX
   

7. PostgreSQL 有许多索引方法，如 BTree、Hash、BRIN、GIST 和 GIN。有时我们想在列上创建一些特定的索引但无法做到这一点。PostgreSQL 存在一些限制，即某些索引无法在某些数据类型和运算符上创建，这也是有道理的。例如，哈希索引只能用于等于运算符。以下查询用于获取特定索引支持的数据类型列表。

   SELECT amname,
          opfname
   FROM   pg_opfamily,
          pg_am
   WHERE  opfmethod = pg_am.oid
   AND    amname = 'btree';
   
   amname | opfname
   --------+--------------------
   btree  | array_ops
   btree  | bit_ops
   btree  | bool_ops
   …
   

8. 此查询将找到未使用的索引。如果index_scans 为0 或接近0，那么您可以删除这些索引。但要小心，因为这些索引可能有独特的用途。

   SELECT s.relname AS table_name,
          indexrelname AS index_name,
          i.indisunique,
          idx_scan AS index_scans
   FROM   pg_catalog.pg_stat_user_indexes s,
          pg_index i
   WHERE  i.indexrelid = s.indexrelid;
   table_name       | index_name            | indisunique | index_scans
   ------------------+-----------------------+-------------+-------------
   pgbench_branches | pgbench_branches_pkey | t           | 0
   pgbench_tellers  | pgbench_tellers_pkey  | t           | 0
   pgbench_accounts | pgbench_accounts_pkey | t           | 0
   (3 rows)
   

9. 用于查找重复索引的查询。在此示例中，pgbench_accounts 有两个相同的索引。一个表上不需要有多个具有不同名称的相同索引。正如我们已经讨论过的，在更新/插入的情况下，所有索引都会与实际表一起更新，这会损害性能。

   SELECT   indrelid::regclass table_name,
            att.attname column_name,
            amname index_method
   FROM     pg_index i,
            pg_class c,
            pg_opclass o,
            pg_am a,
            pg_attribute att
   WHERE    o.oid = ALL (indclass) 
   AND      att.attnum = ANY(i.indkey)
   AND      a.oid = o.opcmethod
   AND      att.attrelid = c.oid
   AND      c.oid = i.indrelid
   GROUP BY table_name, 
            att.attname,
            indclass,
            amname, indkey
   HAVING count(*) > 1;
   table_name | column_name | index_method
   ------------+-------------+--------------
   foo        | a           | btree
   
   (1 row)
   

总结

PostgreSQL 有系统表来存储索引信息，因此，我们可以根据需要编写任意数量的查询。本博客展示了一些基本查询，并展示了如何使用系统表来编写查询。