Date: Fri, 10 May 2002 19:18:42 -0500 (CDT)
From: Brian McCane <bmccane@mccons.net>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: VACUUM FULL
In-Reply-To: <NEBBLAAHGLEEPCGOBHDGKECGEPAA.nickf@ontko.com>
Message-ID: <20020510185436.F62674-100000@fw.mccons.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


Okay, I guess I misunderstood something about "VACUUM FULL".  My
understanding was that a VACUUM (without FULL), marked unused records for
reuse.  VACUUM FULL moved records from the end of a file to the holes
where unused records were stored and truncated the file to free disk
space.  So far so good, but....

I have had continued loss of performance on one of my largest tables
(600,000,000 records).  There are 4 fields in a record like such:

CREATE TABLE foo (
  a int not null references bar(a) on delete cascade on update no action,
  b int not null references baz(b) on delete cascade on update no action,
  c int,
  d smallint,
  primary key(a, b)) ;
CREATE INDEX foo_ac ON foo (a,c) ;
CREATE INDEX foo_ad on foo (a,d) ;
And there are 3 triggers which fire before insert/delete/update.

I INSERT/UPDATE/DELETE approximately 300,000 records per day, but this
number is increasing on a daily basis as I make changes which improve the
performance of my data gathering tools (spiders ;).  Two days ago, it had
reached the point where a search for a 3-word term (ie. free news servers)
took about 60 seconds.  I have just spent 48 hours running a VACUUM FULL
on my table, and now the same search takes < 10 seconds.  I assume that
the increase in performance is due to the decrease in table/index size
which added up to approximate 1GB of freed space on the machine, which was
approximately 4% of the original size of the table and all its indices.
But, a 4% decrease in size should not add up to a 84% increase in
performance (is that right? I always get the ratio confused :).

If all that VACUUM FULL did was move records from file 12345678.6 to file
12345678, the database would still being doing a large number of random
accesses on the table.  However, if VACUUM FULL clusters the data
according to the primary key, it would still be doing a large number of
random access on the table, because the primary key has almost nothing to
do with how I actually access the data in real life.  So, is VACUUM FULL
looking somewhere in pg_statistics (or pg_stat_user_indexes), to determine
which index I actually use most (foo_ad), and then clustering the data
that way, or is there some other agent at work here.

- brian


Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"