Re: slow full table update

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: firerox(at)centrum(dot)cz, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: slow full table update
Date: 2008-11-13 00:13:23
Message-ID: 491B7123.3090309@fuzzy.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

> This is the critical point. You have this line:
>
> There were 132969 unused item pointers.
>
> Which says there's 132k or so dead rows in your table. Which means
> vacuum / autovacuum isn't keeping up. Did you try and stop the update
> several times? Each time it starts then gets killed it creates dead
> rows.

Try to run just ANALYZE on the table and then run the

SELECT relpages, reltuples FROM pg_class WHERE relname = 'table'

again. It should report about 20k of pages, i.e. 160MB. That might slow
the things down ;-)

> Try doing a vacuum full followed by a reindex OR a cluster on this
> table and see if that helps.

Well, maybe the vacuum will fix the problem - have you executed the
query that took 167ms (according to the explain analyze output posted by
you) over a clean table? But I doubt the growth from 6.000 to 20.000
alone might cause degradation from 170ms to several minutes ...

regards
Tomas

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2008-11-13 00:20:11 Re: slow full table update
Previous Message Scott Marlowe 2008-11-12 23:44:07 Re: Disk usage question