Обсуждение: reducing bloat in pg_statistic

I'm looking at a postgresql 7.3 database that has gotten rather bloated
in pg_statistic:

VACUUM verbose pg_statistic;
INFO:  --Relation pg_catalog.pg_statistic--
INFO:  Index pg_statistic_relid_att_index: Pages 4420; Tuples 1590:
Deleted 3789.CPU 0.33s/0.03u sec elapsed 0.96 sec.
INFO:  Removed 3789 tuples in 203 pages.CPU 0.01s/0.03u sec elapsed 0.06 sec.
INFO:  Pages 80345: Changed 7, Empty 0; Tup 1580: Vac 3789, Keep 25,
UnUsed 1566169.Total CPU 7.12s/0.58u sec elapsed 150.03 sec.
INFO:  --Relation pg_toast.pg_toast_16408--
INFO:  Pages 16: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 75.Total CPU 0.00s/0.00u sec elapsed 0.11 sec.
VACUUM

I am trying to figure out a way to shrink this down to something more
reasonable, with the caveat of not restarting the database server.

Vacuum Full doesnt work because it blocks all the queries on the system,
basically running the machine out of connections after a minute or so. 
I also cannot truncate, reindex, or cluster the table as it is a system
table. I even tried some evil hackery like trying to rename the table
and create a new copy in a transaction all with no luck. 

One person suggested that I delete all the rows and then vacuum full it,
but as far as i can tell this would still block the planner from
accessing it while the vacuum full took place, so I'd be out of
connections. 

So I guess the first question is does anyone see any alternative scheme
for trimming this table down to size?

The secondary question is, if I can schedule a restart, is there a way
to get it shrunken with one restart? I was thinking that doing
stats_reset_on_server_start = true might work, can anyone confirm that?

TIA,


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> writes:
> I'm looking at a postgresql 7.3 database that has gotten rather bloated
> in pg_statistic:
> I am trying to figure out a way to shrink this down to something more
> reasonable, with the caveat of not restarting the database server.

You haven't got too many options in 7.3, but it might work reasonably
well to dodelete from pg_statistic;vacuum full pg_statistic;re-analyze to repopulate
vacuum full with no records should take well under a minute.  It won't
shrink the index, but it'll fix the table bloat which seems the worst
part.

The main gotcha here is that any queries started before you can finish
the re-analyze will not have the benefit of statistics; in the worst
case they might choose bad enough plans that you'll wish you had not
done it.
        regards, tom lane