Re: Slow table update - SOLVED!

[Laszlo Nagy <gandalf(at)shopzeus(dot)com>]

> 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.)
8.3.5.

The colum that was being updated is part of one small index only.

> 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.
We tried to remove all indexes on a test system and the update was 
speedy. We are going to try to reduce the row size also move static 
description/name/textual data into a separate table, and leave 
frequently updated data in the original one. We tested this theoretical 
version:

Query returned successfully: 182752 rows affected, 56885 ms execution time.

This is much faster. However, this table is used by hundreds of 
programs. Anyway, I got the answer to my question.

Thank you!

  Laszlo