
Hi,

I'm running PostgreSQL 6.5.3 on Linux-2.2.12 on a 2-way SMP machine.
I noticed that even after VACUUMing there are some indexes that still
remain very big.

For example, here's what I found for pg_attribute:

-rw-------   1 postgres postgres   122880 Apr  4 15:24 pg_attribute
-rw-------   1 postgres postgres 17055744 Apr  4 15:24
pg_attribute_attrelid_index
-rw-------   1 postgres postgres 50176000 Apr  4 15:24
pg_attribute_relid_attnam_index
-rw-------   1 postgres postgres 20758528 Apr  4 15:24
pg_attribute_relid_attnum_index

I know I can fix this by recreating the indexes, but I don't know how to
do it for a system table and if it's safe.

Any hints ?

TIA.

Regards.

Tue, 04 Apr 2000 18:54:19 +0200
Daniele Orlandi <daniele@orlandi.com>
Indexes growing continuously
Daniele Orlandi wrote:
> 
> Hi,
> 
> I'm running PostgreSQL 6.5.3 on Linux-2.2.12 on a 2-way SMP machine.
> I noticed that even after VACUUMing there are some indexes that still
> remain very big.
> 
> For example, here's what I found for pg_attribute:
> 
> -rw-------   1 postgres postgres   122880 Apr  4 15:24 pg_attribute
> -rw-------   1 postgres postgres 17055744 Apr  4 15:24
> pg_attribute_attrelid_index
> -rw-------   1 postgres postgres 50176000 Apr  4 15:24
> pg_attribute_relid_attnam_index
> -rw-------   1 postgres postgres 20758528 Apr  4 15:24
> pg_attribute_relid_attnum_index
> 
> I know I can fix this by recreating the indexes, but I don't know how to
> do it for a system table and if it's safe.
> 
> Any hints ?

Unfortunately, this is a bug in PostgreSQL with respect to system
indexes. You can safely drop/create user indexes, but not system
ones. The only way to reclaim the space used is to dump/reload
your database. Under 7.0, I *believe* Hiroshi's REINDEX command
(please correct me someone if I'm wrong) will allow you to
reconstruct system indexes, but the root problem still exists...

Hope that helps, 

Mike Mascari

Tue, 04 Apr 2000 16:22:56 -0400
Mike Mascari <mascarm@mascari.com>
Re: Indexes growing continuously
> > I know I can fix this by recreating the indexes, but I don't know how to
> > do it for a system table and if it's safe.
> > 
> > Any hints ?
> 
> Unfortunately, this is a bug in PostgreSQL with respect to system
> indexes. You can safely drop/create user indexes, but not system
> ones. The only way to reclaim the space used is to dump/reload
> your database. Under 7.0, I *believe* Hiroshi's REINDEX command
> (please correct me someone if I'm wrong) will allow you to
> reconstruct system indexes, but the root problem still exists...

pg_upgrade will allow this, without dump/reload of data.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 4 Apr 2000 16:47:19 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Indexes growing continuously