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?
4 Answers
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.
-
5Note that index names are only unique per schema. You may want to add
AND schemaname = 'myschema'
. Oct 2, 2015 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
-s
to exclude data and, if known, the table name with-t
.– dezsoOct 2, 2015 at 14:15
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.
More simply if you want them all (all the indexes) ...
=# SELECT indexdef FROM pg_indexes;
CONCURRENTLY
to theCREATE INDEX
command, so you don't take an exclusive lock on the table.