Hi,
I have a postgresql database (8.4) running in production whose performance is degrading.
There have been substantial improvements in performance monitoring in newer versions, so using 8.4 limits your options.
There is no single query that underperforms, all queries do.
Another interesting point is that a generic performance test (https://launchpad.net/tpc-b) gives mediocre peformance when run on the database, BUT the same test on a newly created database, on the same pg cluster, on the same tablespace, does perform good.
Is the server still running its production workload while you do these test, or are you running it on a clone or during off-peak hours? If the former, then if you do your test in a clone which has no load other than the benchmark, do you still see the same thing.
Also, have you tried running pgbench, which also has a tpc-b-ish workload? People on this list will probably be more familiar with that than with the one you offer. What was the size of the test set (and your RAM) and the number of concurrent connections it tests?
Cheers,
Jeff
