From: Bealach-na Bo <bealach_na_bo@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Why so slow?
Date: Fri, 28 Apr 2006 17:31:30 +0000
Message-ID: <BAY101-F1871B68D4D2782C7F21732ADB20@phx.gbl> (raw)
In-Reply-To: <200604280802.59696@hal.medialogik.com>
> > INFO: index "job_log_id_pkey" now contains 10496152 row versions in
> > 59665 pages
>
>See the 10496152 above? That means you have 10496152 rows of data in your
>table. If those, only 365000 are alive. That means you have basically
>never vacuumed this table before, correct?
Almost correct :| I have vacuumed this table monthly (obviously not nearly
enough), but it
is basically a log of events of which there are a very large number of each
day.
>
>Every update or delete creates a new dead row. count(*) scans the whole
>table, dead rows included. That's why it takes so long, the table acts as
>though it has 10496152 rows when doing sequential scans.
Oh! This explains my problems.
>
>Do a VACCUM FULL on it or CLUSTER it on on a index, both of which will
>empty
>out all the free space and make it behave as it should. Note; VACUUM FULL
>will take quite a while and requires an exclusive lock on the table.
>CLUSTER also requires an exclusive lock but should be a lot faster for this
>table.
>
>Oh, and get autovacuum setup and working, posthaste.
The exclusive lock is going to cause problems for me since the table is very
active. Is there a way of getting around that or do I need to schedule the
application that accesses this table?
I'm running version 8.0. Is there autovacuum for this version too?
Regards,
Bealach
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: bealach_na_bo@hotmail.com
Subject: Re: Why so slow?
In-Reply-To: <BAY101-F1871B68D4D2782C7F21732ADB20@phx.gbl>
References: <200604280802.59696@hal.medialogik.com> <BAY101-F1871B68D4D2782C7F21732ADB20@phx.gbl>
* 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