PostgreSQL中索引的查看

PostgreSQL中索引的查看

目录

• PostgreSQL中索引的查看
  • 1. 使用 psql 命令行工具
  • 2. 查询系统目录表
  • 3. 查看索引详细信息(适用于greenplum)
  • 4. 查看索引的存储信息
  • 5. 查看无效/重复/未使用的索引
  • 6. 查看索引类型和特性
  • 7. 使用扩展查看更多信息
  • 8.导出所有索引创建语句

PostgreSQL 提供了多种查看索引信息的方法。以下是常用的几种方式：

1. 使用 psql 命令行工具

-- 查看特定表的索引
\d 表名

-- 或
\di 表名

-- 查看所有索引
\di

-- 查看索引详细信息（包括大小、定义等）
\di+

2. 查询系统目录表

SELECT 
    schemaname AS 模式名,
    tablename AS 表名,
    indexname AS 索引名,
    indexdef AS 索引定义
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') and tablename='employees'
ORDER BY schemaname, tablename, indexname;

3. 查看索引详细信息(适用于greenplum)

包含大小和使用统计

SELECT 
    pi.schemaname AS 模式名,
    pi.tablename AS 表名,
    pi.indexname AS 索引名,
    pg_size_pretty(pg_relation_size(pi.schemaname || '.' || pi.indexname)) AS 索引大小,
    COALESCE(psi.idx_scan, 0) AS 扫描次数,
    COALESCE(psi.idx_tup_read, 0) AS 读取元组数,
    COALESCE(psi.idx_tup_fetch, 0) AS 获取元组数,
    ROUND(
        CASE 
            WHEN COALESCE(psi.idx_scan, 0) > 0 
            THEN COALESCE(psi.idx_tup_read, 0)::numeric / COALESCE(psi.idx_scan, 1) 
            ELSE 0 
        END, 2
    ) AS 平均每次扫描读取行数,
    pi.indexdef AS 索引定义
FROM pg_indexes pi
LEFT JOIN pg_stat_user_indexes psi 
    ON pi.schemaname = psi.schemaname 
    AND pi.tablename = psi.relname
    AND pi.indexname = psi.indexrelname::regclass::text
WHERE pi.schemaname NOT IN ('pg_catalog', 'information_schema')
    AND pi.tablename NOT LIKE 'pg_%'
ORDER BY pg_relation_size(pi.schemaname || '.' || pi.indexname) DESC;

查看索引列的信息

SELECT
    t.relname AS 表名,
    i.relname AS 索引名,
    a.attname AS 列名,
    ix.indisunique AS 是否唯一,
    ix.indisprimary AS 是否主键
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
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 t.relname = 'employees'
ORDER BY
    t.relname,
    i.relname;

4. 查看索引的存储信息

SELECT
    nspname AS 模式名,
    relname AS 索引名,
    pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS 大小,
    relpages AS 页数,
    reltuples AS 估计行数,
    relfilenode AS 文件节点
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'i'  -- 只查看索引
    AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(nspname || '.' || relname) DESC;

5. 查看无效/重复/未使用的索引

查找重复索引

SELECT 
    indrelid::regclass AS 表名,
    array_agg(indexrelid::regclass) AS 重复索引
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;

查找可能未使用的索引

SELECT 
    schemaname AS 模式,
    relname AS 表名,
    indexrelname AS 索引名,
    idx_scan AS 扫描次数,
    pg_size_pretty(pg_relation_size(indexrelid)) AS 索引大小
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- 从未被使用
ORDER BY pg_relation_size(indexrelid) DESC;

6. 查看索引类型和特性

SELECT
    i.relname AS 索引名,
    t.relname AS 表名,
    am.amname AS 索引方法,
    CASE 
        WHEN ix.indisunique THEN 'UNIQUE'
        WHEN ix.indisprimary THEN 'PRIMARY KEY'
        ELSE ''
    END AS 约束类型,
    pg_get_indexdef(i.oid) AS 索引定义,
    ix.indisclustered AS 是否聚簇索引
FROM
    pg_index ix
    JOIN pg_class i ON i.oid = ix.indexrelid
    JOIN pg_class t ON t.oid = ix.indrelid
    JOIN pg_am am ON i.relam = am.oid
WHERE t.relname = 'employees';

7. 使用扩展查看更多信息

如果安装了 pgstattuple 扩展：

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 查看索引的详细统计信息
SELECT * FROM pgstatindex('索引名');

8.导出所有索引创建语句

SELECT 
    'CREATE INDEX ' || indexname || ' ON ' || schemaname || '.' || tablename || 
    ' USING ' || split_part(indexdef, ' USING ', 2) || ';' AS create_index_stmt
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');