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

Re: Indexes growing continuously

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Daniele Orlandi <daniele(at)orlandi(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Indexes growing continuously
Date: 2000-04-04 20:22:56
Message-ID: 38EA4F20.64084704@mascari.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2000-04-04 20:47:19
Subject: Re: Indexes growing continuously
Previous:From: Bruce MomjianDate: 2000-04-04 20:15:11
Subject: Re: postgres crash on CURSORS

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