Re: Temporarily disable all table indices

["George Pavlov" <gpavlov(at)mynewplace(dot)com>]

a (possibly slightly more user-friendly) alternative to the catalog
table is pg_dump, e.g.:

pg_dump -d your_db_name -t your_table -s | grep 'CREATE INDEX' 



> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org 
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of 
> Dmitry Koterov
> Sent: Tuesday, March 27, 2007 3:10 AM
> To: Erik Jones
> Cc: Postgres General
> Subject: Re: [GENERAL] 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 
> <mailto: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 
> <http://www.postgresql.org/docs/8.2/interactive/view-pg-indexe
s.html> ) to dynamically determine what indexes a table has.
> 	
> 	
> 	
> 	erik jones <erik(at)myemma(dot)com>
> 	software developer
> 	615-296-0838
> 	emma(r)
> 
> 
> 
> 
> 
>