postgresql 如何查询PostgreSQL中索引的元数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4138911/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query the metadata of indexes in PostgreSQL
提问by Jon
I need to be able to query a PostgreSQL database to obtain information about the indexes present and their details.
我需要能够查询 PostgreSQL 数据库以获取有关现有索引及其详细信息的信息。
On SQL Server, I can do the following to get a list of all tables/indexes/columns for all indexes:
在 SQL Server 上，我可以执行以下操作来获取所有索引的所有表/索引/列的列表：
select TABLE_NAME, INDEX_NAME, NON_UNIQUE, COLUMN_NAME
from INFORMATION_SCHEMA.STATISTICS
where TABLE_SCHEMA = 'my_schema'
order by TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX
It seems that the STATISTICS table of INFORMATION_SCHEMA is a SQL Server extension. How can I do the equivalent in PostgreSQL?
INFORMATION_SCHEMA 的 STATISTICS 表似乎是 SQL Server 扩展。我怎样才能在 PostgreSQL 中做同样的事情？
EDIT: I'm specifically trying to return a denormalized result set as follows
编辑：我特别想返回一个非规范化的结果集如下
TableName, IndexName, UniqueFl, ColumnName
So I get a row back for each column in all indexes.
所以我为所有索引中的每一列返回一行。
Thanks, Jon
谢谢，乔恩
回答by Andy Lester
What metadata are you looking for?
您在寻找什么元数据？
There are all sorts of swell things you can find out, if you know what you're looking for. For example, here's a dump of index stats and metadata.
如果您知道自己在寻找什么，那么您可以找到各种各样的膨胀的东西。例如，这是索引统计信息和元数据的转储。
SELECT *, pg_size_pretty(pg_relation_size(indexrelname::text))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
Digging through the postgresql wiki will turn up all sorts of good stuff.
挖掘 postgresql wiki 会发现各种各样的好东西。
回答by Gavin
I don't think this is possible from the information_schema see this discussion. Indexes created other than from constraints won't be in the information schema.
我认为这不可能从 information_schema 看到这个讨论。从约束以外创建的索引不会在信息模式中。
However from the system tables you can see this question
但是从系统表中你可以看到这个问题
回答by xacinay
The query i'm using to see the list of indexes and it's actual size:
我用来查看索引列表及其实际大小的查询：
SELECT relname AS name,
reltuples as count, (c.relpages * (8192 /1024) / 1024 ) as size_mb,
c.relfilenode::regclass, cast(c.oid::regclass as TEXT), c.relnatts, c.relkind
FROM pg_class c, pg_namespace n
WHERE
n.nspname ='MyNamespace'
and n.oid = c.relnamespace
and c.relkind = 'i'
ORDER BY c.relpages DESC;
回答by Frank Heikens
Check these views in PostgreSQL about the stats:
检查 PostgreSQL 中有关统计信息的这些视图：
http://www.postgresql.org/docs/current/static/information-schema.htmlhttp://www.postgresql.org/docs/current/static/monitoring-stats.html
http://www.postgresql.org/docs/current/static/information-schema.htmlhttp://www.postgresql.org/docs/current/static/monitoring-stats.html
