Thread: Temporarily disable all table indices
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?
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?
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@myemma.com>
software developer
615-296-0838
emma(r)
Thanks!
pg_indexes.indexdef is exactly what I was looking for!
pg_indexes.indexdef is exactly what I was looking for!
On 3/27/07, Erik Jones < erik@myemma.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@myemma.com>software developer615-296-0838emma(r)
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@postgresql.org > [mailto:pgsql-general-owner@postgresql.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@myemma.com > <mailto:erik@myemma.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@myemma.com> > software developer > 615-296-0838 > emma(r) > > > > > >