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?
There is actually, just query the pg_indexes system catalog view as follows:
SELECT indexdef FROM pg_indexes WHERE indexname = '...'
and you should get back the SQL statement used to define it.
-
4Note that index names are only unique per schema. You may want to add
AND schemaname = 'myschema'. – Erwin Brandstetter Oct 2 '15 at 17:29
Yes, full SQL statement to recreate index is in the system catalog. The simplest way I can think of is using pg_dump/pg_restore:
$ pg_dump -F c | pg_restore -I <your_index_name>
-
4If the database is big, this can be an overkill :) You might want to add
-sto exclude data and, if known, the table name with-t. – dezso Oct 2 '15 at 14:15
More simply if you want them all (all the indexes) ...
=# SELECT indexdef FROM pg_indexes;
indexdef is still not exactly the same as the creation statement in the case of a partial index. For example if we create an index with the following statement:
CREATE INDEX item_orgunit_idx ON items (orgunit_id) WHERE type IN ('invoice', 'purchaseorder', 'beanpayment');
postgres will generate the following indexdef:
CREATE INDEX item_orgunit_idx ON public.items USING btree (orgunit_id) WHERE ((type)::text = ANY ((ARRAY['invoice'::character varying, 'purchaseorder'::character varying, 'beanpayment'::character varying])::text[]))
Although postgres indexdef has all the inferred types and is probably better, our ORM is comparing the two index's where clause and thinks it's different when we generate migration scripts. Which is a problem for us.
CONCURRENTLYto theCREATE INDEXcommand, so you don't take an exclusive lock on the table. – Craig Ringer Oct 3 '15 at 13:09