I run VACUUM (not FULL though) every night, which I thought would be
enough for good query performance. Moreover, the data in table does not
really change significantly since database usage is pretty low right
now, therefore I thought that VACUUM FULL was an overkill.
I think that creating, populating and dropping schemas in the master
database could have affected the query performance and required VACUUM
FULL.
I will definitely look at max_fsm_relations and max_fsm_pages parameter
settings.
Thank you.
Oleg Lebedev
-----Original Message-----
From: Robert Treat [mailto:xzilla@users.sourceforge.net]
Sent: Monday, February 24, 2003 9:59 AM
To: Oleg Lebedev
Cc: Josh Berkus; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] slow query
On Mon, 2003-02-24 at 10:59, Oleg Lebedev wrote:
> Thanks everybody for your help.
> VACUUM FULL did the job, and now the query performance is the same in
> both databases. I am surprised that FULL option makes such a dramatic
> change to the query performance: from 4min. to 5sec.!!! It also
> changed planner stats from ~9 sec to ~8sec.
If your seeing wildly dramatic improvments from vacuum full, you might
want to look into running regular vacuums more often (especially for
high turnover tables), increase your max_fsm_relations to 1000, and
increasing your max_fsm_pages.
Robert Treat
*************************************
This email may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Any review, copying, printing, disclosure or other use is prohibited.
We reserve the right to monitor email sent through our network.
*************************************