Re: Observation about db response time

On Tue, 2005-08-30 at 08:13 -0500, Frank Wiles wrote:
> On Tue, 30 Aug 2005 18:35:30 +0530
> "Akshay Mathur" <akshay.mathur@airtightnetworks.net> wrote:
>
> > Hello Friends,
> >
> > We were having a database in pgsql7.4.2 The database was responding
> > very slowly even after full vacuum analyze (select count(*) from
> > some_table_having_18000_records was taking 18 Sec).
> >
> > We took a backup of that db and restored it back. Now the same db on
> > same PC is responding fast (same query is taking 18 ms).
> >
> > But we can't do the same as a solution of slow response. Do anybody
> > has faced similar problem? Is this due to any internal problem of
> > pgsql? Is there any clue to fasten the database?
>
>   This could be because you don't have max_fsm_pages and
>   max_fsm_relations setup correctly or are not doing full vacuums
>   often enough.
>
>   If your database deletes a ton of data as a matter of course then
>   sometimes a full vacuum will not clear up as much space as it could.
>
>   Try increasing those configuration values and doing vacuums more
>   often.
>
>   If you should also explore upgrading to the latest 8.0 as you will
>   no doubt see noticeable speed improvements.

This can also be caused by index bloat.  VACUUM does not clear out the
index.  You must use REINDEX for that.

-jwb