如何列出所有索引及其類型（BTREE、BRIN、HASH 等…）

我正在查詢 Postgresql 9.6.4 中的系統目錄

獲取表的結果集及其索引很簡單，我缺少的是索引類型（BTREE、BRIN 等）。我似乎無法在系統目錄中的任何位置找到索引類型。

如何查詢目錄以獲取索引列表及其類型？

Postgresql 中的索引類型儲存在pg_am目錄表中。因此，要使用索引類型（或 PostgreSQL 所稱的訪問方法（“am”））獲取所有表及其索引的列表，您可以執行以下命令

SELECT tab.relname, cls.relname, am.amname
FROM pg_index idx 
JOIN pg_class cls ON cls.oid=idx.indexrelid
JOIN pg_class tab ON tab.oid=idx.indrelid
JOIN pg_am am ON am.oid=cls.relam;

簡單的

只需使用系統目錄資訊功能 pg_get_indexdef(index oid)：

重建索引的創建命令。（這是反編譯的重構，不是命令的原文。）

所以就：

SELECT pg_get_indexdef(indexrelid) AS index_definition
FROM   pg_catalog.pg_index;
                                                                                                                                                                             index_definition                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX pg_foreign_data_wrapper_oid_index ON pg_catalog.pg_foreign_data_wrapper USING btree (oid)
CREATE UNIQUE INDEX pg_foreign_server_oid_index ON pg_catalog.pg_foreign_server USING btree (oid)
CREATE UNIQUE INDEX pg_user_mapping_oid_index ON pg_catalog.pg_user_mapping USING btree (oid)
...

這包括 index方法（您所要求的）。Postgres 14 的手冊：

方法

要使用的索引方法的名稱。選項有btree、hash、 gist、spgist、gin和brin。預設方法是btree.

…以及：運算符類（僅當不是預設值時）、排序規則（僅當不是預設值時）、模式（表始終是模式限定的）、表和索引名稱。

您可能需要的一切。可能還不止這些，因為上面的簡單查詢包括系統目錄的許多索引。

僅來自使用者領域的索引

SELECT pg_get_indexdef(i.indexrelid) AS index_definition
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
WHERE  n.nspname !~ '^pg_'
AND    c.relkind = 'i';

                                                                                      index_definition                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX b2_pkey ON public.b2 USING btree (big_id)
CREATE INDEX big_big_fts_gin_idx ON public.big USING gin (to_tsvector('simple'::regconfig, big))
...

過濾器AND c.relkind = 'i'是多餘的，但可以使查詢更快。

（相關查詢通常也會添加AND n.nspname <> 'information_schema'。但由於 中沒有索引，information_schema我們可以跳過它。）

豪華版

您可能需要專用列中的數據和頂部有意義的順序：

SELECT n.nspname AS schema
    , i.indrelid::regclass::text AS table
    , c.relname AS index
    , a.amname AS index_method
    , opc.operator_classes
    , pg_get_indexdef(i.indexrelid) AS index_definition
FROM   pg_catalog.pg_namespace n
JOIN   pg_catalog.pg_class     c ON c.relnamespace = n.oid
JOIN   pg_catalog.pg_index     i ON i.indexrelid = c.oid
JOIN   pg_catalog.pg_am        a ON a.oid = c.relam
CROSS  JOIN LATERAL (
  SELECT ARRAY (SELECT opc.opcname
                FROM   unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
                JOIN   pg_opclass opc ON opc.oid = o.oid
                ORDER  BY o.ord)
  ) opc(operator_classes)
WHERE  n.nspname !~ '^pg_'
AND    c.relkind = 'i'
ORDER  BY 1, 2, 3, 4;

schema |       table       |             index             | index_method |          operator_classes           |                                                                                       index_definition                                                                                        
--------+-------------------+-------------------------------+--------------+-------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public | b2                | b2_pkey                       | btree        | {int4_ops}                          | CREATE UNIQUE INDEX b2_pkey ON public.b2 USING btree (big_id)
public | big               | big_big_fts_gin_idx           | gin          | {tsvector_ops}                      | CREATE INDEX big_big_fts_gin_idx ON public.big USING gin (to_tsvector('simple'::regconfig, big))
...

從目錄表中獲取索引方法pg_am，並從pg_opclass. 您可以添加排序規則…

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/186944