From: Alan Hodgson <ahodgson@simkin.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Why so slow?
Date: Fri, 28 Apr 2006 08:02:59 -0700
Message-ID: <200604280802.59696@hal.medialogik.com> (raw)
In-Reply-To: <BAY101-F1673984E069F3892A67D05ADB20@phx.gbl>
References: <BAY101-F1673984E069F3892A67D05ADB20@phx.gbl>
On April 28, 2006 04:41 am, "Bealach-na Bo" <bealach_na_bo@hotmail.com>
wrote:
> 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?
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.
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.
--
No long, complicated contracts. No actuarial tables to pore over. Social
Security operates on a very simple principle: the politicians take your
money from you and squander it "
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: ahodgson@simkin.ca
Subject: Re: Why so slow?
In-Reply-To: <200604280802.59696@hal.medialogik.com>
* 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