PG_INDEXES

PG_INDEXES displays access to useful information about each index in the database.

Table 1 PG_INDEXES columns

Name

Type

Reference

Description

schemaname

name

PG_NAMESPACE.nspname

Name of the schema that contains tables and indexes

tablename

name

PG_CLASS.relname

Name of the table for which the index serves

indexname

name

PG_CLASS.relname

Index name

tablespace

name

PG_TABLESPACE.spcname

Name of the tablespace that contains the index

indexdef

text

-

Index definition (a reconstructed CREATE INDEX command)

Example

Query the index information about a specified table.

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)

Query information about indexes of all tables in a specified schema in the current database.

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)