11

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?

21

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.

  • 4
    Note that index names are only unique per schema. You may want to add AND schemaname = 'myschema'. – Erwin Brandstetter Oct 2 '15 at 17:29
0

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>
  • 4
    If 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. – dezso Oct 2 '15 at 14:15
-1

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

=# SELECT indexdef FROM pg_indexes;
-1

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.

  • This doesn't answer the question at all. – Laurenz Albe Jan 13 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.