Re: Temporarily disable all table indices

Thanks!

*pg_indexes.indexdef* is exactly what I was looking for!

On 3/27/07, Erik Jones <erik(at)myemma(dot)com> wrote:
>
> 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)
>
>
>
>