Prabhjot Sheena <prabhjot.she...@rivalwatch.com> wrote: > PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 > 20060404 (Red Hat 3.4.6-9)
Running anything on that version is insane. Not only has the 8.3 major release been out of support since February of 2013, but you're missing about 4 years of fixes for serious bugs and security issues since the last patch version of 8.3 before it went out of support. Any vacuum or planning problems you are seeing on that release are very likely fixed on a supported version. > This is what i did to improve query performance. i recreated all > the indexes on work_unit table and have been running vacuum > analyze through cron job 3 times a day on two tables that are in > the query. The query performance is between 2 to 3 seconds now. That's pretty solid evidence that you have been having problems with bloat. The most common cause of this is not setting autovacuum to be aggressive enough. > autovacuum_max_workers = 1 Vacuum of one big table could starve all other tables, resulting in bloat. If anything, this should be set larger than the default. You might want to try setting it to somewhere in the 3 to 5 range, but then watch for any long periods where all workers are busy. If you see that, you probably need more workers. I would also boost autovacuum_vacuum_cost_limit to maybe 500. > The strange thing i noticed is that just today at one time query > performance came down to under 1 second and started using this > query plan There's really no point hypothesizing about what might cause that in such an old version with so many known bugs. The best way to improve performance would be to upgrade. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
