| From: | "Dmitry Koterov" <dmitry(at)koterov(dot)ru> |
|---|---|
| To: | "Postgres General" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Temporarily disable all table indices |
| Date: | 2007-03-26 22:24:44 |
| Message-ID: | d7df81620703261524nf42670di39e9985dcf807d0@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter Eisentraut | 2007-03-26 22:50:50 | Re: PG over NFS |
| Previous Message | Matthijs Melissen | 2007-03-26 22:05:56 | Problem with transactions |