Re: Is Vacuum/analyze destroying my performance?

["Matthew T. O'Connor" <matthew@xxxxxxxx>]

Carlo Stonebanks wrote:
> > Just a wild guess, but the performance problem sounds like maybe as your 
> > data changes, eventually the planner moves some query from an index scan 
> > to a sequential scan, do you have any details on what queries are taking 
> > so long when things are running slow?  You can turn on the GUC var 
> > "log_min_duration_statement" and see what queries are slow and then 
> > manually check them with an explain analyze, that might help.
> >     
> This is pretty well what I think is happening - I expect all queries to 
> eventually move from seq scans to index scans. I actually have a SQL logging 
> opion built into the import app.
>
> I just can't figure out how the planner can be so wrong. We are running a 4 
> CPU server (two dual core 3.2 GHz Xeons) with 4GB RAM and Windows Server 
> 2003 x64 and a PERC RAID subsystem (I don't know the RAID type). I know that 
> the metrics for the planner can be changed - is the default config for 
> postgesql not suitable for our setup? For this server, we would like to be 
> optimised for high speed over a few connections, rather than the classic 
> balanced speed over many connections. 

If it is the planner choosing a very bad plan, then I don't think your 
hardware has anything to do with it.  And, we can't diagnose why the 
planner is doing what it's doing without a lot more detail.  I suggest 
you do something to figure out what queries are taking so long, then 
send us an explain analyze, that might shine some light on the subject.