PG中的索引查询和分析
Tags： PGPostgreSQL优化索引
👉 本文共约577个字，系统预计阅读时间或需3分钟。
查询索引
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | CREATE OR REPLACE VIEW index_check AS SELECT t.schemaname, t.tablename, c.reltuples::bigint AS num_rows, pg_size_pretty(pg_relation_size(c.oid)) AS table_size, psai.indexrelname AS index_name, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", psai.idx_scan AS number_of_scans, psai.idx_tup_read AS tuples_read, psai.idx_tup_fetch AS tuples_fetched, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as create_index FROM pg_tables t LEFT JOIN pg_class c ON t.tablename = c.relname LEFT JOIN pg_index i ON c.oid = i.indrelid LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema') AND pg_relation_size(i.indexrelid) > 0 ORDER BY 1, 2; -- 索引信息查询 select * from index_check where tablename = 'sys_log'; schemaname | tablename | num_rows | table_size | index_name | index_size | unique | number_of_scans | tuples_read | tuples_fetched | create_index ------------+-----------+----------+------------+-----------------------+------------+--------+-----------------+-------------+----------------+----------------------------------------------------------------------- public | big | 30000000 | 1716 MB | idx_big_user_id | 643 MB | N | 1 | 1 | 1 | CREATE INDEX idx_big_user_id ON big USING btree (user_id) public | big | 30000000 | 1716 MB | idx_big_ctime | 643 MB | N | 3 | 3 | 0 | CREATE INDEX idx_big_ctime ON big USING btree (ctime) public | testtab | 10000 | 3936 kB | testtab_unchanged_idx | 344 kB | N | 0 | 0 | 0 | CREATE INDEX testtab_unchanged_idx ON testtab USING btree (unchanged) public | testtab | 10000 | 3936 kB | testtab_pkey | 248 kB | Y | 600000 | 1203729 | 600000 | CREATE UNIQUE INDEX testtab_pkey ON testtab USING btree (id) public | testtab | 10000 | 3936 kB | testtab_changed_idx | 240 kB | N | 0 | 0 | 0 | CREATE INDEX testtab_changed_idx ON testtab USING btree (changed) public | u | 100000 | 3544 kB | uj | 1152 kB | N | 100010 | 500010 | 0 | CREATE INDEX uj ON u USING btree (j) (6 rows) -- 索引统计 SELECT relname AS index_name, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan DESC; |
上面的语句可以发现索引是否持续的被使用，我们可以建立一个索引的历史分析表，每天将表中的内容插入到历史表中，然后定期分析，通过历史分析表中,同一个索引，不同时间的增量来判断这个索引是否还在被使用，专业也是一个发现无用索引的方案。
1 你的系统数据库运行到当前时间的长度，因为系统的表中的数据会伴随你系统的重启而清零，所以你得程序设计的逻辑中必须考虑这点
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
