Re: Slow table update

Laszlo Nagy wrote:
> >> My other idea was that there are so many indexes on this table, maybe
> >> the update is slow because of the indexes?
> >>
> >
> > Updating indexes is certainly very far from being free. How many is
> > "many"?
> >
> Number of indexes = 15.
>
> 3 indexex are on "text" type column, 500MB in size each.
> Other are on int8 and timestamp columns, cca. 200MB each.

To me, that's "many" ;-)

That's a lot when you think about what happens when indexed columns are changed, deleted or inserted -- a lot of background work that the database has to do.

Inf 8.3 the HOT feature may help if the columns being updated are indexed ... what version of PostgreSQL is this again ? (Forgive my lack of memory -- the last few days I've forgotten a lot, heh heh.)

Any chances to reduce those to a bare minimum, perhaps using conditional index strategies or even some form of replication, so the primary uses indexes related to the updates and the mirror uses indexes related to the read-only / reporting needs ? Perhaps some form of staging table with no indexes to load, check data, etc. and then insert.

Any way to reduce those ? Check the usage via the system stats on table/index use and try removing some and testing to see what makes a difference.

HTH

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)