Thank's for your response. The situation is that the top result is when the server is already exhibiting problems.
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. I forget to mention that php scrip is executed as a web application (Apache web server 2.2.3, php installed as a Server API Apache 2.0 Handler) called periodically each 8 seconds. After the restart of the postgres server everything works fine for several hours, the web application has a fast response when opening a web page. But after some time postmaster process (sometimes two postmaster process both owned by postgres user) rises and response time of the web application becomes slow, i.e. opening a php page with postgres access last for 8-10 seconds or even more. The php configuration for the postgres is default PostgreSQL Support enabled PostgreSQL(libpq) Version 8.1.8 Multibyte character support enabled SSL support enabled Active Persistent Links 1 Active Links 1 Directive Local Value Master Value pgsql.allow_persistent On On pgsql.auto_reset_persistent Off Off pgsql.ignore_notice Off Off pgsql.log_notice Off Off pgsql.max_links Unlimited Unlimited pgsql.max_persistent Unlimited Unlimited >> If that's what it looks like your server is running just fine. Load >> of 1.31, 85+% idle, no wait time. Or is that top and vmstat output >> from when the server is running fine? > > Don't forget that there are 8 CPUs, and the backend will only run on one > of them. > > But I concur that this seems ok. > How many rows are returned? Is 0.6 seconds an unacceptable time for that? > > If there is a lot of sorting going on and the pages are residing in the > buffer, I would expect high CPU load. > > Normally, I am quite happy if my database is CPU bound. I start worrying > if I/O wait grows too high. > > Yours, > Laurenz Albe > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
