Hi there!
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
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
