PG_INDEXES¶
PG_INDEXES displays access to useful information about each index in the database.
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)