如何查询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

因此，我为所有索引中的每一列返回一行。

谢谢， 乔恩

Answer 1

您在寻找什么元数据？

如果您知道自己在寻找什么，那么您可以找到各种各样的精彩事物。例如，这是索引统计信息和元数据的转储。

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

深入研究 postgresql wiki 会发现各种好东西。

Answer 2

我认为从 information_schema 来看这是不可能的
请参阅此讨论。除了约束之外创建的索引不会出现在信息模式中。

但是，从系统表中您可以
请参阅这个问题

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;

Answer 4

编辑：由于错误的 SELECT 语句，PostgreSQL 10 破坏了列顺序，修复了查询：

SELECT
    i.relname AS index_name,
    (
        SELECT array_agg(attname)
        FROM (
            SELECT a.attname
            FROM (
                SELECT colnum, row_number() over() AS rownum
                FROM (
                    SELECT unnest(string_to_array(ix.indkey::text, ' ')) AS colnum
                ) AS c
            ) AS b, pg_attribute a
            WHERE a.attrelid = t.oid AND a.attnum = b.colnum::int
            ORDER BY b.rownum
        ) AS d
    ) AS column_names
FROM pg_catalog.pg_class t
JOIN pg_catalog.pg_index ix ON ix.indrelid = t.oid
JOIN pg_catalog.pg_class i ON i.oid = ix.indexrelid
WHERE
    t.relkind = 'r'
    AND t.relnamespace = to_regnamespace('public')
    AND t.relname = 'users'
;

啊，我对此并不感到自豪...

---

原始帖子：

在在整个目录中，我能找到的唯一可以按索引顺序读取索引列的地方是在 pg_index.indkey 列中。

问题在于它是一个 int2vector 类型列，无法转换为数组。唯一的方法是使用 string_to_array() 对以空格分隔的值输出进行字符串分割。

然后，您需要取消嵌套结果并与 pg_attribute 联接以获取列名称。

考虑下表：

test_db=# \d users  
                                          Table "public.users"
      Column       |           Type           | Collation | Nullable |              Default              
-------------------+--------------------------+-----------+----------+-----------------------------------
 id                | integer                  |           | not null | nextval('users_id_seq'::regclass)
 org_id            | integer                  |           |          | 
 name              | text                     |           |          | 
 username          | character varying(255)   |           |          | NULL::character varying
 email             | character varying(255)   |           | not null | 
 date              | timestamp with time zone |           |          | CURRENT_TIMESTAMP
 no_constraint_col | text                     |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
Foreign-key constraints:
    "users_org_id" FOREIGN KEY (org_id) REFERENCES org(id) ON DELETE CASCADE
Referenced by:
    TABLE "user_address" CONSTRAINT "user_address_user_id_fk" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

然后让我们添加一个附加索引以用于演示目的：

test_db=# create index on users (id, name, date);

然后，以下目录查询就可以解决问题：

SELECT
    i.relname AS index_name,
    (
        SELECT array_agg(a.attname)
        FROM (SELECT t.oid, unnest(string_to_array(ix.indkey::text, ' ')) AS colnum) AS b
        JOIN pg_attribute a ON
            a.attrelid = b.oid AND a.attnum = b.colnum::int 
    ) AS column_names
FROM pg_catalog.pg_class t
JOIN pg_catalog.pg_index ix ON ix.indrelid = t.oid
JOIN pg_catalog.pg_class i ON i.oid = ix.indexrelid
WHERE
    t.relkind = 'r'
    AND t.relnamespace = to_regnamespace('public')
    AND t.relname = 'users'
;

它将输出：

       index_name       |  column_names  
------------------------+----------------
 users_id_name_date_idx | {id,name,date}
 users_pkey             | {id}
 users_email_key        | {email}
(3 rows)

花了我一段时间才弄好。

Answer 5

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;

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

Answer 6

检查 PostgreSQL 中有关统计信息的这些视图：

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