From: Tomas Vondra <tv@fuzzy.cz>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: firerox@centrum.cz, pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: slow full table update
Date: Thu, 13 Nov 2008 01:13:23 +0100
Message-ID: <491B7123.3090309@fuzzy.cz> (raw)
In-Reply-To: <dcc563d10811121458t63499067ucd50e287d5c24a0@mail.gmail.com>
References: <200811101728.3048@centrum.cz>
<200811101729.14977@centrum.cz>
<200811101730.15902@centrum.cz>
<dcc563d10811121255h7d788032v4a8aa24c9db523b8@mail.gmail.com>
<200811122347.21602@centrum.cz>
<dcc563d10811121458t63499067ucd50e287d5c24a0@mail.gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, scott.marlowe@gmail.com
Subject: Re: slow full table update
In-Reply-To: <491B7123.3090309@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox