Skip to main content
 首页 » 数据库

PostgreSql索引维护教程

2022年07月19日146lvdongjie

上文我们简要介绍了索引,本文补充介绍一些关于索引的维护知识。

索引维护

查看索引大小

有两种方式查看关系大小,关系可以时表或索引。显示表大小,不包括索引:

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 视图直接查询用户表情况。

无用的所用

从两个方面查询无用的索引:

  1. 判断索引是否与其他索引重复或重叠
  2. 基于索引统计信息判断索引是否被使用过

但需要注意的是,唯一索引和主键索引要排除调,因为即使查询时不用我们也需要。

查看索引是否重复:

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
阅读延展