I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new name and then drop the old one. Is there any way to see the SQL statement that was used to create an index so I can just copy that?
PostgreSQL – How to Show Creation Statement for an Index
indexpostgresql
Related Question
- Postgresql – Dropped an index and recreated it in PostgreSQL 9.2, not used anymore
- SQL Server 2012 – Order of DROP_EXISTING and ONLINE for CREATE INDEX
- PostgreSQL 9.3 – Will This SQL Snippet REINDEX GIN Index?
- Postgresql – Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL
- PostgreSQL 9.5 – Why Newest Index Not Used for ORDER BY?
- Postgresql – Is it a good idea to create an index just for new data to increase index creation performance
- Postgresql – Adding an index without a lag (PostgreSQL)
Best Answer
There is actually, just query the
pg_indexes
system catalog view as follows:and you should get back the SQL statement used to define it.