数据仓库服务 GAUSSDB(DWS)-PG_INDEXES:应用示例

时间：2024-06-29 17:51:19

应用示例

查询指定表的索引信息。

  
       SELECT * FROM pg_indexes WHERE tablename = 'mytable';
 schemaname | tablename |   indexname    | tablespace |                                   indexdef
------------+-----------+----------------+------------+-------------------------------------------------------------------------------
 public     | mytable   | idx_mytable_id |            | CREATE INDEX idx_mytable_id ON mytable USING btree (id) TABLESPACE pg_default
(1 row)

查询当前数据库指定模式下所有表的索引信息。

  
       SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename,indexname;
 tablename |     indexname      |                                              indexdef
-----------+--------------------+-----------------------------------------------------------------------------------------------------
 books     | books_pkey         | CREATE UNIQUE INDEX books_pkey ON books USING btree (id) TABLESPACE pg_default
 books     | idx_books_tags_gin | CREATE INDEX idx_books_tags_gin ON books USING gin (tags) TABLESPACE pg_default
 customer  | c_custkey_key      | CREATE UNIQUE INDEX c_custkey_key ON customer USING btree (c_custkey, c_name) TABLESPACE pg_default
 mytable   | idx_mytable_id     | CREATE INDEX idx_mytable_id ON mytable USING btree (id) TABLESPACE pg_default
 test1     | idx_test_id        | CREATE INDEX idx_test_id ON test1 USING btree (id) TABLESPACE pg_default
 v0        | v0_pkey            | CREATE UNIQUE INDEX v0_pkey ON v0 USING btree (c) TABLESPACE pg_default
(6 rows)