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.albe@wien.gv.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?