如何列出PostgreSQL中物化视图上创建的索引？

要查看物化视图上的索引，pg_indexes视图确实只覆盖普通表（relkind='r'），但可以通过直接查询系统表或者创建自定义视图来解决，具体方法如下：

• 直接查询系统表
  执行以下SQL语句，可同时获取普通表和物化视图的索引信息：

  SELECT
      n.nspname AS schemaname,
      c.relname AS tablename,
      i.relname AS indexname,
      pg_get_indexdef(idx.indexrelid) AS indexdef
  FROM
      pg_index idx
      JOIN pg_class i ON idx.indexrelid = i.oid
      JOIN pg_class c ON idx.indrelid = c.oid
      JOIN pg_namespace n ON c.relnamespace = n.oid
  WHERE
      c.relkind IN ('r', 'm') -- 'r'是普通表，'m'是物化视图
      AND n.nspname NOT IN ('pg_catalog', 'information_schema') -- 排除系统内置schema
  ORDER BY
      schemaname, tablename, indexname;
  

• 创建自定义视图复用查询
  如果需要频繁查询，可创建一个自定义视图替代默认的pg_indexes：

  CREATE OR REPLACE VIEW pg_all_indexes AS
  SELECT
      n.nspname AS schemaname,
      c.relname AS tablename,
      i.relname AS indexname,
      pg_get_indexdef(idx.indexrelid) AS indexdef
  FROM
      pg_index idx
      JOIN pg_class i ON idx.indexrelid = i.oid
      JOIN pg_class c ON idx.indrelid = c.oid
      JOIN pg_namespace n ON c.relnamespace = n.oid
  WHERE
      c.relkind IN ('r', 'm')
      AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY
      schemaname, tablename, indexname;
  

  之后直接执行SELECT * FROM pg_all_indexes;就能获取所有普通表和物化视图的索引。

另外，pgAdmin3版本较老，对物化视图的索引支持不完善，若想通过GUI查看，建议升级到pgAdmin4，它能正确展示物化视图的索引列表。

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