We are experiencing gradually worsening performance in PostgreSQL 7.4.7, on a system with the following specs: Linux OS (Fedora Core 1, 2.4 kernal) Flash file system (2 Gig, about 80% full) 256 Meg RAM 566 MHz Celeron CPU We use Orbit 2.9.8 to access PostGres. The database contains 62 tables. When the system is running with a fresh copy of the database, performance is fine. At its worst, we are seeing fairly simple SELECT queries taking up to 1 second to execute. When these queries are run in a loop, the loop can take up to 30 seconds to execute, instead of the 2 seconds or so that we would expect. VACUUM FULL ANALYZE helps, but doesn't seem to eliminate the problem. The following table show average execution time in "bad" performance mode in the first column, execution time after VACUUM ANALYZE in the second column, and % improvement (or degradation?) in the third. The fourth column show the query that was executed. 741.831|582.038|-21.5| ^IDECLARE table_cursor 170.065|73.032|-57.1| FETCH ALL in table_cursor 41.953|45.513|8.5| CLOSE table_cursor 61.504|47.374|-23.0| SELECT last_value FROM pm_id_seq 39.651|46.454|17.2| select id from la_looprunner 1202.170|265.316|-77.9| select id from rt_tran 700.669|660.746|-5.7| ^IDECLARE my_tran_load_cursor 1192.182|47.258|-96.0| FETCH ALL in my_tran_load_cursor 181.934|89.752|-50.7| CLOSE my_tran_load_cursor 487.285|873.474|79.3| ^IDECLARE my_get_router_cursor 51.543|69.950|35.7| FETCH ALL in my_get_router_cursor 48.312|74.061|53.3| CLOSE my_get_router_cursor 814.051|1016.219|24.8| SELECT $1 = 'INSERT' 57.452|78.863|37.3| select id from op_sched 48.010|117.409|144.6| select short_name, long_name from la_loopapp 54.425|58.352|7.2| select id from cd_range 45.289|52.330|15.5| SELECT last_value FROM rt_tran_id_seq 39.658|82.949|109.2| SELECT last_value FROM op_sched_id_seq 42.158|68.189|61.7| select card_id,router_id from rt_valid Has anyone else seen gradual performance degradation like this? Would upgrading to Postgres 8 help? Any other thoughts on directions for troubleshooting this? Thanks...
