Is there a way to show the creation statement for an index in PostgreSQL

Question

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?

Answer 1

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.

Answer 2

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>

Answer 3

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.

Answer 4

More simply if you want them all (all the indexes) ...

=# SELECT indexdef FROM pg_indexes;