pg_statistic_relid_att_index

Hi all,
I've a problem with the pg_statistic_relid_att_index system index. The server
version is 7.1.3 (so reindex is not available). This database is heavily
updated,inserted,deleted and thus vacuumed. Since 1,5 year it runned perfectly
but since the beginning of the we've got slow select. In many case, as the
system goes slow, it often a question of table size. So I looked at the oid
sizes in my data directory and the filenode corresponding to the
pg_statistic_relid_att_index get about 87MB of my disk whereas other object
remains quite small. I presume this index is not cleaned while vacuuming
pg_statistic table (with or without analyze). My problem is a performance
problem. I would avoid to upgrade database to 7.3 (some upgrade would be done
soon and I do not really have time to spend now for such a task) and I would
like to make this index slimmer as possible to get the performance back (for
this database speed is a critical factor).

Thanks in advance for any help/hints :)

Le Wed, 25 Jun 2003 03:25:46 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com> me disait que :

Drop and recreate the index is your only solution given the constraints
you have.

Thanks for your quick answer. :)

I agree on this procedure to get rid of the reluctant indices but as
pg_statistic_relid_att_index is a system index it seems to put another problem
in the bucket : An error occurs when I try to remove this index....

supervisor=# drop index pg_statistic_relid_att_index;
ERROR: index "pg_statistic_relid_att_index" is a system index

If I try to do the same on template1 database, I get the same result :/

Regards

"the West won the world not by the superiority of its ideas or values or
religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
- Samuel P. Huntington

On Wed, Jun 25, 2003 at 04:14:10AM -0600, scott.marlowe wrote:

Looks like you'll have to dump and restore your database. :-(

Nothing so drastic. 7.1 has reindex (7.0 has to 7.1 must have it too). To do
it on system indexes you need to run it as single user mode. Start the
a postgres process with -P which allows you to reindex system indexes.
Probably drop them too, if you want to.

On Wed, 25 Jun 2003, Jean-Christophe ARNU wrote:

Le Wed, 25 Jun 2003 03:25:46 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com> me disait que :

Drop and recreate the index is your only solution given the constraints
you have.

Thanks for your quick answer. :)

I agree on this procedure to get rid of the reluctant indices but as
pg_statistic_relid_att_index is a system index it seems to put another problem
in the bucket : An error occurs when I try to remove this index....

supervisor=# drop index pg_statistic_relid_att_index;
ERROR: index "pg_statistic_relid_att_index" is a system index

If I try to do the same on template1 database, I get the same result :/

Regards

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)