Re: UPDATE slow

On Tue, 4 Feb 2003, John Smith wrote:

>
> That works - updates on foo take about 1.4 seconds. I dropped all the indexes and fk's on stats and updates there take about 2.8 seconds. These are on the cygwin machine.
> Looking at the tables, foo doesn't have any modifiers for the columns (stats does - not null default 0).
> John
> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:On Tue, 4 Feb 2003, John Smith wrote:
>
> > Think that makes sense ;). However, I just dropped all the foreign key
> > constraints on stats and there wasn't any speed increase in the query.
> > I've also tried dropping the index on clicks (no speed-up there
> > either).
>
> Can you duplicate it with a new table?
>
> Something like:
>
> create table foo as select * from stats;
> update foo set clicks=3344;

Is this a table that got a lot of updates en masse? If it was regularly
vacuumed but you still had a problem, it might be index bloat. Keep an
eye on it, and if the table starts to get slow, try reindexing the indexes
on that table and see if that happens.

The "fix" for this problem is to crank up max fsm settings, and run vacuum
more often, but that doesn't always actually fix things.