We have a dev machine running 9.0.1 (an i3 laptop, with a regular hard disk, with 4GB of RAM, and a mostly untuned postgresql.conf file). The changed lines are:
shared_buffers = 512MB
temp_buffers = 48MB
--
Anthony
work_mem = 32MB
maintenance_work_mem = 348MB
checkpoint_segments = 10
effective_cache_size = 512MB
The same database is loaded onto a production server running 9.1.1 (dual QC processors, RAID-10 SAS drives, 36GB of RAM), which replicates to a backup server. This has a lot of changed properties:
shared_buffers = 8500MB
work_mem = 35MB
maintenance_work_mem = 512MB
wal_level = hot_standby
checkpoint_segments = 50
max_wal_senders = 3
wal_keep_segments = 144
random_page_cost = 1.0
effective_cache_size = 16384MB
effective_io_concurrency = 6
The same DB is loaded on both the production and the dev environment, and in all cases (about 5000 distinct different queries), the production environment is about 500x faster, except for one type of query (both databases were loaded from the same pg_dump on an 8.4.4 database):
On the dev box, we have: http://explain.depesz.com/s/rwU - about 131 seconds
On the production box, we have: http://explain.depesz.com/s/3dt - about .25 seconds
For the life of me, I don't understand why it would be slower. What can we do to speed up this one query?
By the way, on 8.4.4, the query took about 84 seconds. I cannot understand why the 9.0 is so blazing fast, but 8.4.4 and 9.1.1 are slower. We've checked the query results (they are identical) to make sure we're not missing any data.
Anthony
