用于PostgreSQL索引维护的有用查询-526互联

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

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

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

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

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)

众所周知，索引是一种性能特征，但除此之外，它还用于确保唯一性。但为了确保唯一性，我们需要一种单独类型的索引，称为唯一索引。为了检查索引是否唯一，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)

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

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

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

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：这是一个特殊函数，给出表及其所有索引的总大小。

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

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)

如果您的索引损坏或膨胀，您需要重新构建该索引。同时，您不想阻止表上的操作，因此您可以选择 REINDEX CONCURRENTLY 命令。
```
REINDEX INDEX CONCURRENTLY idx;
REINDEX
```
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
…
```

此查询将找到未使用的索引。如果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)

用于查找重复索引的查询。在此示例中，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 有系统表来存储索引信息，因此，我们可以根据需要编写任意数量的查询。本博客展示了一些基本查询，并展示了如何使用系统表来编写查询。