如何在PostgreSQL中查询BTree索引的INCLUDE列与过滤条件

修正后的查询语句 #

SELECT 
    s.nspname AS "TABLE_SCHEMA",
    tc.relname AS "TABLE_NAME",
    ic.relname AS "INDEX_NAME",
    m.amname AS "INDEX_TYPE",
    -- 构建键列列表（含排序、COLLATE信息）
    (SELECT STRING_AGG(
        a.attname ||  
        CASE WHEN i.indoption[a.attnum - 1] = 3 THEN ' DESC' ELSE ' ASC' END ||
        CASE WHEN i.indcollation[a.attnum - 1] != 0 THEN ' COLLATE ' || (SELECT collname FROM pg_collation WHERE oid = i.indcollation[a.attnum - 1]) ELSE '' END,
        ', '
    )
    FROM pg_attribute AS a
    WHERE a.attrelid = i.indexrelid
      AND a.attnum <= i.indnkeyatts) AS "KEY_COLUMN_LIST",
    i.indisunique AS "UNIQUE",
    i.indisprimary AS "PRIMARY_KEY",
    i.indisclustered AS "CLUSTERED",
    i.indisvalid AS "VALID",
    CASE k.contype 
        WHEN 'c' THEN 'CHECK'
        WHEN 'f' THEN 'FOREIGN KEY'
        WHEN 'p' THEN 'PRIMARY KEY'
        WHEN 'u' THEN 'UNIQUE'
    END AS "CONSTRAINT_TYPE",
    -- 获取INCLUDE列列表
    (SELECT STRING_AGG(a2.attname, ', ')
     FROM pg_attribute AS a2
     WHERE a2.attrelid = i.indexrelid
       AND a2.attnum > i.indnkeyatts) AS "INCLUDE_COLUMN_LIST",
    -- 获取过滤条件（部分索引）
    pg_get_expr(i.indpred, i.indrelid) AS "FILTER_DEFINITION",
    pg_get_indexdef(i.indexrelid) AS "INDEX_DEFINITION"
FROM pg_index AS i
JOIN pg_class AS ic ON i.indexrelid = ic.oid
JOIN pg_class AS tc ON i.indrelid = tc.oid          
JOIN pg_namespace AS s ON tc.relnamespace = s.oid
JOIN pg_am AS m ON ic.relam = m.oid
LEFT OUTER JOIN pg_constraint AS k 
    ON i.indexrelid = k.conindid AND i.indkey[0:i.indnkeyatts-1] = k.conkey
WHERE m.amname = 'btree'
ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;

关键逻辑说明 #

1. INCLUDE列获取：
   PostgreSQL中，索引的键列数量存储在pg_index.indnkeyatts中，索引对应的pg_attribute里，attnum大于该值的列就是INCLUDE的列，直接筛选聚合即可。

2. 过滤条件获取：
   带过滤条件的部分索引，其过滤规则存储在pg_index.indpred字段中，用pg_get_expr函数可以将其解析为可读的SQL表达式，无需手动解析索引定义。

测试验证 #

针对你创建的测试表和索引：

• 索引X2的INCLUDE_COLUMN_LIST会返回D, E, F
• 索引X3的FILTER_DEFINITION会返回(g > '2020-01-01'::date)

内容的提问来源于stack exchange，提问作者SQLpro