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(dot)mathur(at)airtightnetworks(dot)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