Обсуждение: 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 :)
--
Jean-Christophe ARNU
Drop and recreate the index is your only solution given the constraints you have. On Wed, 25 Jun 2003, Jean-Christophe ARNU wrote: > 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
--
Jean-Christophe ARNU
Looks like you'll have to dump and restore your database. :-( 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 > >
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) -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "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
Вложения
Jean-Christophe ARNU (JX) <arnu@paratronic.fr> writes:
> 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).
You're overdue for an update then. Have you read the lists of bugs
fixed since 7.1?
regards, tom lane
Oh, good. I was under the impression that 7.1.3 didn't have reindex (that's was Jean said at first, and I haven't used it in so long, I wasn't sure either.) Jean, don't forget to BACKUP first. On Wed, 25 Jun 2003, Martijn van Oosterhout wrote: > 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) > >