上文我们简要介绍了索引，本文补充介绍一些关于索引的维护知识。
索引维护
查看索引大小
有两种方式查看关系大小，关系可以时表或索引。显示表大小，不包括索引：
select pg_size_pretty(pg_relation_size('film'));
-- 432 kB
查看表大小包括索引：
select pg_size_pretty(pg_total_relation_size('film'));
-- 688 kB
同样可以单独查看索引大小：
select pg_size_pretty(pg_relation_size('film_fulltext_idx'));
-- 88 kB
顺便说下，也可以查看整个数据库：
SELECT pg_size_pretty(pg_database_size('dvdrental'));
-- 15 MB
查看前10个最大关系信息：
SELECT relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 10;
可能的返回结果为：
| relation | size |
|---|---|
| rental | 1272 kB |
| payment | 920 kB |
| idx_unq_rental_rental_date_inventory_id_customer_id | 512 kB |
| pg_toast_2618 | 440 kB |
| film | 432 kB |
| rental_pkey | 368 kB |
| idx_fk_inventory_id | 368 kB |
| idx_fk_rental_id | 336 kB |
| idx_fk_staff_id | 336 kB |
| payment_pkey | 336 kB |
查看前5个最大表（包括索引）：
SELECT relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;
可能的结果为：
| relation | total_size |
|---|---|
| rental | 2544 kB |
| payment | 2288 kB |
| film | 688 kB |
| film_actor | 576 kB |
| inventory | 472 kB |
更新索引
在PostgreSQL中执行CREATE INDEX命令时，可以使用CONCURRENTLY 参数并行创建索引，使用CONCURRENTLY参数不会锁表，创建索引过程中不会阻塞表的更新、插人、删除操作。由于PostgreSQL的 MVCC 内部机制，当运行大量的更新操作后，会有“索引膨胀”的现象，这时候可以通过CREATE INDEX CONCURRENTLY 在不阻塞查询和更新的情况下，在线重新创建索引，创建好新的索引之后，再删除原先有膨胀的索引，减小索引尺寸，提高查询速度。对于主键也可以使用这种方式进行重建，重建方法如下:
CREATE UNIQUE INDEX CONCURRENTLY ON mytbl USING btre e (id);
-- CREATE INDEX
此时 id 字段上有两个索引：
SELECT schemaname, relname, indexrelname, pg_relation_size (indexrelid)
AS index_size, idx_scan , idx_tup_read , idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname IN (
SELECT indexname FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'mytbl'
);
下面开启事务删除主键索引，同时更改唯一索引为主键索引：
begin
alter table mytbl drop constraint mytbl_pkey;
alter table mytbl add constraint mytbl_id_idx primary key using index mytbl_id_idx;
end;
查询缺失索引
作为数据库管理员，不仅要给数据库增加索引、周期性监控数据增长情况、分析已有索引，也需要发现缺失索引，更好地提升查询性能。
下我们给出一段脚本，可以获取缺失索引表的报告，开发者可以通过该报告优化数据库查询性能：
SELECT relname AS TableName ,seq_scan-idx_scan AS TotalSeqScan
,CASE WHEN seq_scan-idx_scan > 0
THEN 'Missing Index Found'
ELSE 'Missing Index Not Found'
END AS MissingIndex
,pg_size_pretty(pg_relation_size(relname::regclass)) AS TableSize
,idx_scan AS TotalIndexScan
FROM pg_stat_all_tables
WHERE schemaname='public' AND pg_relation_size(relname::regclass)>100000
ORDER BY 2 DESC;
我们也可以使用 pg_stat_all_tables 视图直接查询用户表情况。
无用的所用
从两个方面查询无用的索引：
- 判断索引是否与其他索引重复或重叠
- 基于索引统计信息判断索引是否被使用过
但需要注意的是，唯一索引和主键索引要排除调，因为即使查询时不用我们也需要。
查看索引是否重复：
SELECT schemaname, relname, indexrelname
FROM pg_stat_user_indexes s JOIN
pg_index i ON s. indexrelid = i. indexrelid
WHERE idx_scan=0 AND NOT indisunique AND NOT indisprimary;
查看重叠的索引：
WITH index_info AS (
SELECT
pg_get_indexdef(indexrelid) AS index_def,
indexrelid::regclass
index_name ,
indrelid::regclass table_name, array_agg(attname order by attnum) AS index_att
FROM pg_index i JOIN
pg_attribute a ON i.indexrelid = a.attrelid
GROUP BY pg_get_indexdef(indexrelid) , indrelid, indexrelid
)
SELECT DISTINCT
CASE WHEN a.index_name > b.index_name THEN a.index_def ELSE b.index_def
END AS index_def,
CASE WHEN a.index_name > b.index_name THEN a.index_name ELSE
b.index_name END AS index_name,
CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def
END AS overlap_index_def,
CASE WHEN a.index_name > b.index_name THEN b.index_name ELSE
a.index_name END AS overlap_index_name,
a.index_att = b.index_att as full_match, a.table_name
FROM index_info a INNER JOIN
index_info b ON (a.index_name!=b.index_name AND a.table_name=b.table_name AND a.index_att && b.index_att);
本文参考链接：https://blog.csdn.net/neweastsun/article/details/120026756
