From: | "George Pavlov" <gpavlov(at)mynewplace(dot)com> |
---|---|
To: | <dmitry(at)koterov(dot)ru> |
Cc: | "Postgres General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Temporarily disable all table indices |
Date: | 2007-03-27 16:17:39 |
Message-ID: | 8C5B026B51B6854CBE88121DBF097A869E0A6B@ehost010-33.exch010.intermedia.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
>
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Bartunov | 2007-03-27 16:22:00 | Re: [GENERAL] Is there a shortage of postgresql skilled ops people |
Previous Message | Tom Lane | 2007-03-27 16:13:19 | Re: Could not create relation: File exists error |