Skip site navigation (1) Skip section navigation (2)

good old VACUUM FULL

From: felix <crucialfelix(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: good old VACUUM FULL
Date: 2011-03-23 00:52:55
Message-ID: AANLkTi=SMx4GiW5DLFTpOm4Go-ibipc6WpTN+4rY7tbF@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
I posted many weeks ago about a severe problem with a table that was
obviously bloated and was stunningly slow. Up to 70 seconds just to get a
row count on 300k rows.

I removed the text column, so it really was just a few columns of fixed
data.
Still very bloated.  Table size was 450M

The advice I was given was to do CLUSTER, but this did not reduce the table
size in the least.
Nor performance.

Also to resize my free space map (which still does need to be done).
Since that involves tweaking the kernel settings, taking the site down and
rebooting postgres and exposing the system to all kinds of risks and
unknowns and expensive experimentations I was unable to do it and have had
to hobble along with a slow table in my backend holding up jobs.

Much swearing that nobody should ever do VACUUM FULL.  Manual advises
against it.  Only crazy people do that.

Finally I decide to stop taking advice.

ns=> explain analyze select count(*) from fastadder_fastadderstatus;
---------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=62602.08..62602.09 rows=1 width=0) (actual
time=25320.000..25320.000 rows=1 loops=1)
   ->  Seq Scan on fastadder_fastadderstatus  (cost=0.00..61815.86
rows=314486 width=0) (actual time=180.000..25140.000 rows=314493 loops=1)
 Total runtime: *25320.000* ms

ns=> vacuum full fastadder_fastadderstatus;

took about 20 minutes

ns=> explain analyze select count(*) from fastadder_fastadderstatus;
----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=7478.03..7478.04 rows=1 width=0) (actual
time=940.000..940.000 rows=1 loops=1)
   ->  Seq Scan on fastadder_fastadderstatus  (cost=0.00..6691.82
rows=314482 width=0) (actual time=0.000..530.000 rows=314493 loops=1)
 Total runtime: *940.000 ms*

moral of the story:  if your table is really bloated, just do VACUUM FULL

CLUSTER will not reduce table bloat in and identical fashion

Responses

pgsql-performance by date

Next:From: ChrisDate: 2011-03-23 04:24:42
Subject: Re: good old VACUUM FULL
Previous:From: Tom LaneDate: 2011-03-22 22:56:59
Subject: Re: Analyze on temp table taking very long

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group