Re: Temporarily disable all table indices

On Mar 26, 2007, at 5:24 PM, Dmitry Koterov wrote:

> Hello.
>
> I need to perform a mass operation (UPDATE) on each table row. E.g.
> - modify one table column:
>
> UPDATE tbl SET tbl_text = MD5(tbl_id);
>
> The problem is that if this table contains a number of indices,
> such UPDATE is very very slow on large table.
>
> I have to drop all indices on the table, then run the update (very
> quick) and after that - re-create all indices back. It is much more
> speedy. Unfortunately the table structure may change in the future
> (e.g. - new indices are added), so I don't know exactly in this
> abstraction layer, what indices to drop and what - to re-create.
>
> Is any way (or ready piece of code) to save all existed indices,
> drop them all and then - re-create after a mass UPDATE?

No, but you can use the pg_indexes view (http://www.postgresql.org/
docs/8.2/interactive/view-pg-indexes.html) to dynamically determine
what indexes a table has.

erik jones <erik(at)myemma(dot)com>
software developer
615-296-0838
emma(r)