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?
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.