本节简单介绍了PostgreSQL中用于索引维护的一些查询. **查看表&索引大小** ``` 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); [local:/data/run/pg12]:5120 pg12@testdb=# SELECT CONCAT(n.nspname,'.', c.relname) AS table, pg12@testdb-# i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size, pg12@testdb-# pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size, pg12@testdb-# pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c pg12@testdb-# JOIN pg_index x ON c.oid = x.indrelid pg12@testdb-# JOIN pg_class i ON i.oid = x.indexrelid pg12@testdb-# LEFT JOIN pg_namespace n ON n.oid = c.relnamespace pg12@testdb-# WHERE c.relkind = ANY (ARRAY['r', 't']) pg12@testdb-# AND n.oid NOT IN (99, 11, 12375); table | index_name | table_size | index_size | total_size ------------------+------------------+------------+------------+------------ public.test | test_pkey | 0 bytes | 8192 bytes | 16 kB public.t_pgbench | idx_t_pgbench_c1 | 425 MB | 214 MB | 639 MB public.tbl1 | tbl1_pkey | 5096 kB | 2208 kB | 7312 kB (3 rows) ``` **索引定义** ``` SELECT pg_get_indexdef(indexrelid) AS index_query FROM pg_index WHERE indrelid = 'test'::regclass; [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_get_indexdef(indexrelid) AS index_query FROM pg_index WHERE indrelid = 'test'::regclass; index_query --------------------------------------------------------------- CREATE UNIQUE INDEX test_pkey ON public.test USING btree (id) (1 row) ``` **识别未使用的Index** ``` 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; [local:/data/run/pg12]:5120 pg12@testdb=# SELECT s.relname AS table_name, pg12@testdb-# indexrelname AS index_name, pg12@testdb-# i.indisunique, pg12@testdb-# idx_scan AS index_scans pg12@testdb-# FROM pg_catalog.pg_stat_user_indexes s, pg12@testdb-# pg_index i pg12@testdb-# WHERE i.indexrelid = s.indexrelid; table_name | index_name | indisunique | index_scans ------------+------------------+-------------+------------- test | test_pkey | t | 0 t_pgbench | idx_t_pgbench_c1 | f | 0 tbl1 | tbl1_pkey | t | 0 (3 rows) ``` **检索重复的索引** ``` 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; [local:/data/run/pg12]:5120 pg12@testdb=# CREATE UNIQUE INDEX test_pkey_dup ON public.test USING btree (id); CREATE INDEX [local:/data/run/pg12]:5120 pg12@testdb=# SELECT indrelid::regclass table_name, pg12@testdb-# att.attname column_name, pg12@testdb-# amname index_method pg12@testdb-# FROM pg_index i, pg12@testdb-# pg_class c, pg12@testdb-# pg_opclass o, pg12@testdb-# pg_am a, pg12@testdb-# pg_attribute att pg12@testdb-# WHERE o.oid = ALL (indclass) pg12@testdb-# AND att.attnum = ANY(i.indkey) pg12@testdb-# AND a.oid = o.opcmethod pg12@testdb-# AND att.attrelid = c.oid pg12@testdb-# AND c.oid = i.indrelid pg12@testdb-# GROUP BY table_name, pg12@testdb-# att.attname, pg12@testdb-# indclass, pg12@testdb-# amname, indkey pg12@testdb-# HAVING count(*) > 1; table_name | column_name | index_method ------------+-------------+-------------- test | id | btree (1 row) ``` **参考资料** [Useful Queries For PostgreSQL Index Maintenance](https://www.percona.com/blog/2020/03/31/useful-queries-for-postgresql-index-maintenance/)