【GaussDB】GaussDB怎么查询分区表的索引信息？

[mimimiquan]

GaussDB怎么查询分区表的索引信息？

[Gauss松鼠会]

分区表上的索引分为:本地(局部)索引(local index) 和 全局索引(global index)

SELECT
n.nspname AS schemaname, --schema名称
c1.relname AS tablename, -- 表名
c2.relname AS indexname, -- 索引名称
s.conname AS conname, -- 约束名称
pg_get_constraintdef(s.oid) AS constraintdef, -- 如果是约束，输出约束定义
CASE WHEN s.conname IS NULL THEN pg_get_indexdef(x.indexrelid) END AS indexdef -- 如果不是约束，输出索引定义
FROM pg_index x
INNER JOIN pg_class c1 ON c1.oid = x.indrelid
INNER JOIN pg_class c2 ON c2.oid = x.indexrelid
INNER JOIN pg_namespace n ON n.oid = c1.relnamespace
LEFT JOIN pg_constraint s ON s.conrelid = x.indrelid AND s.conindid = x.indexrelid
WHERE (x.indisprimary = true OR x.indisunique = true)
AND c1.relkind = 'r'
AND x.indrelid >= 16384 AND x.indexrelid > 16384
AND (c1.reloptions IS NULL OR c1.reloptions::text not like '%internal_mask%') -- 排除内置对象
ORDER BY schemaname, tablename, indexname

尝试使用此sql

[Gauss松鼠会]

1.pg_partition里有

2.或者用pg_get_tabledef查表定义，包含索引创建语句

3.或者查询PG_INDEXES视图