这篇文章主要讲解了“分析PostgreSQL中用于索引维护的查询”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“分析PostgreSQL中用于索引维护的查询”吧！

查看表&索引大小

SELECTCONCAT(n.nspname,'.',c.relname)AStable,i.relnameASindex_name,pg_size_pretty(pg_relation_size(x.indrelid))AStable_size,pg_size_pretty(pg_relation_size(x.indexrelid))ASindex_size,pg_size_pretty(pg_total_relation_size(x.indrelid))AStotal_sizeFROMpg_classcJOINpg_indexxONc.oid=x.indrelidJOINpg_classiONi.oid=x.indexrelidLEFTJOINpg_namespacenONn.oid=c.relnamespaceWHEREc.relkind=ANY(ARRAY['r','t'])ANDn.oidNOTIN(99,11,12375);[local:/data/run/pg12]:5120pg12@testdb=#SELECTCONCAT(n.nspname,'.',c.relname)AStable,pg12@testdb-#i.relnameASindex_name,pg_size_pretty(pg_relation_size(x.indrelid))AStable_size,pg12@testdb-#pg_size_pretty(pg_relation_size(x.indexrelid))ASindex_size,pg12@testdb-#pg_size_pretty(pg_total_relation_size(x.indrelid))AStotal_sizeFROMpg_classcpg12@testdb-#JOINpg_indexxONc.oid=x.indrelidpg12@testdb-#JOINpg_classiONi.oid=x.indexrelidpg12@testdb-#LEFTJOINpg_namespacenONn.oid=c.relnamespacepg12@testdb-#WHEREc.relkind=ANY(ARRAY['r','t'])pg12@testdb-#ANDn.oidNOTIN(99,11,12375);table|index_name|table_size|index_size|total_size------------------+------------------+------------+------------+------------public.test|test_pkey|0bytes|8192bytes|16kBpublic.t_pgbench|idx_t_pgbench_c1|425MB|214MB|639MBpublic.tbl1|tbl1_pkey|5096kB|2208kB|7312kB(3rows)

索引定义

SELECTpg_get_indexdef(indexrelid)ASindex_queryFROMpg_indexWHEREindrelid='test'::regclass;[local:/data/run/pg12]:5120pg12@testdb=#SELECTpg_get_indexdef(indexrelid)ASindex_queryFROMpg_indexWHEREindrelid='test'::regclass;index_query---------------------------------------------------------------CREATEUNIQUEINDEXtest_pkeyONpublic.testUSINGbtree(id)(1row)

识别未使用的Index

SELECTs.relnameAStable_name,indexrelnameASindex_name,i.indisunique,idx_scanASindex_scansFROMpg_catalog.pg_stat_user_indexess,pg_indexiWHEREi.indexrelid=s.indexrelid;[local:/data/run/pg12]:5120pg12@testdb=#SELECTs.relnameAStable_name,pg12@testdb-#indexrelnameASindex_name,pg12@testdb-#i.indisunique,pg12@testdb-#idx_scanASindex_scanspg12@testdb-#FROMpg_catalog.pg_stat_user_indexess,pg12@testdb-#pg_indexipg12@testdb-#WHEREi.indexrelid=s.indexrelid;table_name|index_name|indisunique|index_scans------------+------------------+-------------+-------------test|test_pkey|t|0t_pgbench|idx_t_pgbench_c1|f|0tbl1|tbl1_pkey|t|0(3rows)

检索重复的索引

SELECTindrelid::regclasstable_name,att.attnamecolumn_name,amnameindex_methodFROMpg_indexi,pg_classc,pg_opclasso,pg_ama,pg_attributeattWHEREo.oid=ALL(indclass)ANDatt.attnum=ANY(i.indkey)ANDa.oid=o.opcmethodANDatt.attrelid=c.oidANDc.oid=i.indrelidGROUPBYtable_name,att.attname,indclass,amname,indkeyHAVINGcount(*)>1;[local:/data/run/pg12]:5120pg12@testdb=#CREATEUNIQUEINDEXtest_pkey_dupONpublic.testUSINGbtree(id);CREATEINDEX[local:/data/run/pg12]:5120pg12@testdb=#SELECTindrelid::regclasstable_name,pg12@testdb-#att.attnamecolumn_name,pg12@testdb-#amnameindex_methodpg12@testdb-#FROMpg_indexi,pg12@testdb-#pg_classc,pg12@testdb-#pg_opclasso,pg12@testdb-#pg_ama,pg12@testdb-#pg_attributeattpg12@testdb-#WHEREo.oid=ALL(indclass)pg12@testdb-#ANDatt.attnum=ANY(i.indkey)pg12@testdb-#ANDa.oid=o.opcmethodpg12@testdb-#ANDatt.attrelid=c.oidpg12@testdb-#ANDc.oid=i.indrelidpg12@testdb-#GROUPBYtable_name,pg12@testdb-#att.attname,pg12@testdb-#indclass,pg12@testdb-#amname,indkeypg12@testdb-#HAVINGcount(*)>1;table_name|column_name|index_method------------+-------------+--------------test|id|btree(1row)

感谢各位的阅读，以上就是“分析PostgreSQL中用于索引维护的查询”的内容了，经过本文的学习后，相信大家对分析PostgreSQL中用于索引维护的查询这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！