Hi all,
We have recently upgraded our project with a huge DB from Postgres v9.1 to v9.4. The whole system performance has degraded alarmingly after the upgrade. Simple operations that were taking only a few seconds in Postgres 9.1 are now taking minutes of time.
The problem is not specific to one query orany particular kind of query. Its been generic and overall system has become very slow.
We tried running 'VACUUM ANALYZE' on the DB and that seemed to be helpful too. But the improvement after this is nowhere close to the performance we had in 9.1.
We tried changing some of the performance parameters in the postgres.confirm as follows (our Postgres server has an 8GB RAM) -
shared_buffers = 200MB
maintenance_work_mem = 1000MB
default_statistics_target = 1000
effective_cache_size = 4000MB
And these made absolutely no difference to the query execution time.
The strangest part of the problem is when I EXPLAIN ANALYZE the same query multiple times in the same Postgres server, it gives me different execution times every time ranging from 45 ms to 181 ms.
We are absolutely clueless on how to proceed. Any help would be greatly appreciated.
Thanks in advance.