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.
1 2 3 4 5 |
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.
1 2 3 4 5 6 7 8 9 10 |
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) |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot