如何查询PostgreSQL索引的元数据

Question

我需要能够查询PostgreSQL数据库以获取有关现有索引及其详细信息的信息。

在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

似乎INFORMATION_SCHEMA的STATISTICS表是SQL Server的扩展。我该如何在PostgreSQL中实现相同功能？

编辑：我特别想返回一个非规范化的结果集，如下所示。

TableName, IndexName, UniqueFl, ColumnName

“因此，我会针对所有索引中的每一列返回一行。”

“谢谢， Jon”

- Jon

Answer 1

您需要查找哪些元数据？

如果您知道要查找什么，就可以找到各种有用的信息。例如，这里是索引统计数据和元数据的转储。

SELECT *, pg_size_pretty(pg_relation_size(indexrelname::text))
    FROM pg_stat_all_indexes 
    WHERE schemaname = 'public'

挖掘PostgreSQL维基将会发现各种有用的内容。

- Andy Lester

---

谢谢，我已经添加了一个编辑来准确展示我的需求。这更多是元数据而不是统计数据。 - Jon

Answer 2

我认为从information_schema中无法实现这一点，可以参考这个讨论。除了从约束创建的索引之外，其他创建的索引都不会出现在信息模式中。

但是，从系统表中可以看到这个问题。

- Gavin

---

谢谢 - 链接的问题正是我所需要的。 - Jon

Answer 3

我使用的查询语句用于查看索引列表及其实际大小：

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;

- xacinay

Answer 4

PostgreSQL没有提供“INFORMATION_SCHEMA.STATISTICS”视图。 但是我们可以像这样查询一些元数据：

select 
    t.relname as table_name,
    i.relname as index_name,
    m.amname as index_type,
    case ix.indisunique when 'f' then 'NO' else 'YES' end UNIQUENESS,
    case ix.indisprimary when 'f' then 'NO' else 'YES' end IS_PRIMARY,
    case ix.indisclustered when 'f' then 'NO' else 'YES' end IS_CLUSTERED,
    case ix.indisvalid when 'f' then 'NO' else 'YES' end IS_VALID,
    a.attname as column_name
from pg_namespace n,
    pg_am m,
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where n.oid=t.relnamespace
    and m.oid=i.relam
    and t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and n.nspname=?
    and t.relkind = 'r'
    and t.relname=?
    and i.relname not in (select conname from pg_constraint)
order by t.relname, i.relname, a.attnum;

由于主键/唯一键/检查/排除约束可能会默认生成索引，因此我们应该过滤系统生成的索引。

- Y.C.1024

Answer 5

在PostgreSQL中查看这些统计信息：

http://www.postgresql.org/docs/current/static/information-schema.html http://www.postgresql.org/docs/current/static/monitoring-stats.html

- Frank Heikens

---

谢谢，我已经查看了第一个链接，但没有找到我需要的内容。我不认为第二个链接是我想要的。 - Jon