Re: CPU load

It would be useful to confirm that this is a backend process.
With top, hit the 'c' key to show the full path / description of the
process.
Backend postgres processes should then have more useful descriptions of what
they are doing and identifying themselves.
You can also confirm what query is causing that by lining up the process id
from top with the one returned by:

select current_query, procpid from pg_stat_activity where current_query not
like '<IDLE%';

Or by simply using the process id for the where clause (where procpid = ).

How often is the table being queried modified? Between the startup when the
query is fast, and when it slows down, is there a lot of modification to its
rows?

On Fri, Sep 26, 2008 at 5:52 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> kiki wrote:
> > The number of rows returned by the query varies, right now is:
> >
> > 49 row(s)
> > Total runtime: 3,965.718 ms
> > The table currently has 971582 rows.
> >
> > But the problem is that when database server is restarted everything
> works
> > fine and fast. No heavy loads of the processor and as time passes
> > situation with the processor is worsen.
>
> It would be interesting to know the result of EXPLAIN ANALYZE for the
> query, both when it performs well and when it doesn't.
>
> One thing I see right away when I look at your postgresql.conf is that
> you have set shared_buffers to an awfully small value of 2000, when you
> have
> enough memory on the machine (vmstat reports 2GB free memory, right?).
>
> Does the situation improve if you set it to a higher value?
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>