Re: Can anyone explain this: duplicate dbs.

От: SpaceBallOne
Тема: Re: Can anyone explain this: duplicate dbs.
Дата: ,
Msg-id: BAY14-DAV16F2B796085B6591A93AFACC0E0@phx.gbl
(см: обсуждение, исходный текст)
Ответ на: Can anyone explain this: duplicate dbs.  ("SpaceBallOne")
Ответы: Re: Can anyone explain this: duplicate dbs.  (Christopher Kings-Lynne)
Re: Can anyone explain this: duplicate dbs.  (John A Meinel)
Список: pgsql-performance

Скрыть дерево обсуждения

Can anyone explain this: duplicate dbs.  ("SpaceBallOne", )
 Re: Can anyone explain this: duplicate dbs.  (Christopher Kings-Lynne, )
 Re: Can anyone explain this: duplicate dbs.  (Christopher Kings-Lynne, )
  Re: Can anyone explain this: duplicate dbs.  (Tom Lane, )
 Re: Can anyone explain this: duplicate dbs.  (John A Meinel, )
  Re: Can anyone explain this: duplicate dbs.  (Tom Lane, )
  Re: Can anyone explain this: duplicate dbs.  (Bruno Wolff III, )
 Re: Can anyone explain this: duplicate dbs.  ("SpaceBallOne", )
  Re: Can anyone explain this: duplicate dbs.  (Christopher Kings-Lynne, )
  Re: Can anyone explain this: duplicate dbs.  (John A Meinel, )

> What version of postgres?

8.0.2 ... but I think I've seen this before on 7.3 ...

> There are a few possibilities. If you are having a lot of updates to the
> table, you can get index bloat. And vacuum doesn't fix indexes. You have
> to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
> FULL has, so you need to be a little careful with it.

> Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
> your table ends up nicer when you are done.

Thanks, will try those next time this problem crops up (i just deleted /
recreated the database to speed things for its users in the office ...
probably should have held off to see if I could find a solution first!).

Yes, the database / table-in-question does have a lot of updates, deletes,
and new rows (relatively speaking for a small business).

Would CLUSTER / REINDEX still have an effect if our queries were done via
sequential scan? This is a old database (as in built by me when i was just
starting to learn unix / postgres) so the database design is pretty horrible
(little normalisation, no indexes).

Have taken Chris's advice onboard too and setup cron to do a vacuumdb hourly
instead of my weekly vacuum.

Cheers,

Dave.





В списке pgsql-performance по дате сообщения:

От: Bruno Wolff III
Дата:
Сообщение: Re: Can anyone explain this: duplicate dbs.
От: Philip Hallstrom
Дата:
Сообщение: Re: Need help to decide Mysql vs Postgres