PostgreSQL 索引信息

在 PostgreSQL 的系统目录pg_catalog（System Catalog）中，pg_class、pg_index、pg_attribute、pg_am 等表存储了数据库的元数据信息。以下是这些表在查询中的具体作用详解，结合一个典型的查询示例：

---

查询示例场景

假设我们需要查询某个表（如 users）的所有索引信息，包括索引类型、索引列、存储方式等。通常会涉及以下系统表的关联查询：

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    am.amname AS index_type,  -- 索引类型（如btree、hash等）
    a.attname AS column_name,  -- 索引列名
    ix.indisunique AS is_unique  -- 是否唯一索引
FROM
    pg_class t  -- 存储表信息
    JOIN pg_index ix ON t.oid = ix.indrelid  -- 关联表的索引信息
    JOIN pg_class i ON ix.indexrelid = i.oid  -- 存储索引本身的信息
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)  -- 关联索引列
    JOIN pg_am am ON i.relam = am.oid  -- 索引的访问方法（存储类型）
WHERE
    t.relname = 'users';

---

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name,
    am.amname AS index_type
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a,
    pg_am am
WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND am.oid = i.relam
    AND t.relname = 't_table_mytable' --表名
ORDER BY
    t.relname, i.relname;

---

各表的作用详解

1. pg_class t（存储表/索引的元数据）

• 作用：存储数据库中的表、索引、视图等关系（relation）的元数据。
• 关键字段：
  • oid：对象的唯一标识符（主键）。
  • relname：表/索引的名称。
  • relkind：对象类型（r=普通表，i=索引，v=视图等）。
  • relam：关联到 pg_am.oid，表示表的存储方式（如堆表、TOAST表）。
• 在查询中：
  • t 代表目标表（如 users），通过 t.relname 过滤表名。
  • ix.indrelid = t.oid 表示索引属于该表。

---

2. pg_class i（存储索引本身的元数据）

• 作用：与 pg_class t 结构相同，但专门存储索引的信息（因为索引在 PostgreSQL 中也是一种“关系”）。
• 关键字段：
  • oid：索引的唯一标识符。
  • relname：索引名称。
  • relam：关联到 pg_am.oid，表示索引的访问方法（如 B-tree、Hash）。
• 在查询中：
  • i 代表索引对象，通过 ix.indexrelid = i.oid 关联到 pg_index。
  • am.amname 通过 i.relam = am.oid 获取索引类型。

---

3. pg_index ix（存储索引与表的关联信息）

• 作用：记录索引与表的映射关系，以及索引的属性（如是否唯一、索引列等）。
• 关键字段：
  • indrelid：关联到 pg_class.oid，表示索引所属的表。
  • indexrelid：关联到 pg_class.oid，表示索引本身的标识符。
  • indkey：数组类型，存储索引列在表中的列号（attnum）。
  • indisunique：是否唯一索引（true/false）。
• 在查询中：
  • ix.indrelid = t.oid 关联表和索引。
  • ix.indkey 通过 ANY() 与 pg_attribute.attnum 匹配，找到索引列。

---

4. pg_attribute a（存储列的元数据）

• 作用：存储表或索引的列信息（如列名、数据类型、列号等）。
• 关键字段：
  • attrelid：关联到 pg_class.oid，表示列所属的表/索引。
  • attnum：列在表中的序号（从 1 开始）。
  • attname：列名。
• 在查询中：
  • a.attrelid = t.oid 关联到目标表。
  • a.attnum = ANY(ix.indkey) 匹配索引列（indkey 是数组，ANY 检查是否包含）。

---

5. pg_am am（存储访问方法信息）

• 作用：记录表或索引的存储/访问方法（如 B-tree、Hash、GiST 等）。
• 关键字段：
  • oid：访问方法的唯一标识符。
  • amname：访问方法名称（如 btree、hash）。
• 在查询中：
  • i.relam = am.oid 获取索引的存储类型（如 btree 表示 B-tree 索引）。

---

查询逻辑总结

1. 从 pg_class t 找到目标表（如 users）。
2. 通过 pg_index ix 找到该表的所有索引（ix.indrelid = t.oid）。
3. 通过 pg_class i 获取索引本身的元数据（如索引名 i.relname）。
4. 通过 pg_am am 获取索引的存储类型（如 btree）。
5. 通过 pg_attribute a 找到索引列（匹配 ix.indkey 和 a.attnum）。

---

扩展：其他常用系统表

• pg_namespace：存储模式（schema）信息，用于过滤特定模式下的对象。

  JOIN pg_namespace ns ON t.relnamespace = ns.oid AND ns.nspname = 'public'
  

• pg_type：存储数据类型信息，可用于查询列的数据类型。

  JOIN pg_type ty ON a.atttypid = ty.oid
  

---

实际应用示例

查询所有表的索引及其列：

SELECT
    ns.nspname AS schema_name,
    t.relname AS table_name,
    i.relname AS index_name,
    am.amname AS index_type,
    string_agg(a.attname, ', ') AS indexed_columns,
    ix.indisunique AS is_unique
FROM
    pg_class t
    JOIN pg_namespace ns ON t.relnamespace = ns.oid
    JOIN pg_index ix ON t.oid = ix.indrelid
    JOIN pg_class i ON ix.indexrelid = i.oid
    JOIN pg_am am ON i.relam = am.oid
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
WHERE
    ns.nspname NOT LIKE 'pg_%' AND ns.nspname != 'information_schema'
GROUP BY
    schema_name, table_name, index_name, index_type, is_unique
ORDER BY
    schema_name, table_name, index_name;

---

总结

• pg_class：存储表/索引的元数据（名称、OID、类型等）。
• pg_index：记录索引与表的关联关系（如索引列、唯一性）。
• pg_attribute：存储列的详细信息（列名、序号）。
• pg_am：定义表/索引的存储/访问方法（如 B-tree）。

通过关联这些表，可以查询 PostgreSQL 数据库的完整元数据信息，包括表结构、索引、约束等。