We recently upgraded a few of our development computers from PostgreSQL 8.2 to 9.1, with plans on eventually upgrading our production servers. Everything seems well with the restore of the databases, and the local websites on the dev computers. What isn't working are some reporting ETL queries.
A particular query runs in under 30 seconds on 8.2. The core query (we've broken it out) is simply a SELECT statement with 15 joins. For some reason, when this query is run on 9.1, PostgreSQL spikes in memory usage, acquiring all the memory available on the machine, which is 2.8 GB on my machine. Consequently, the query does not finish in any reasonable amount of time (up to 17 mins compared to 30 secs, haven't let it run forever because I need to be able to use my computer).
What we have done so far:
- Replicated config settings for 8.2 to 9.1
- Ran EXPLAIN on the query on both 8.2 and 9.1: should run faster on
Explain 8.2 on explain.depesz.com (Raw data: explain_8.2.dat)
Explain 9.1 on explain.depesz.com (Raw data: explain_9.1.dat)
- Broken the query down by removing joins. When below any 10 joins, the query will complete
- Examined the joining tables for irregularities
- Tested the query on different machines running 9.1
- Reviewed Postgres config documentation and changelog
- Ran VACUUM and ANALYZE on the db
- Rebooted the machine
- Updated kernel SHMMAX when postgres wouldn't start after reboot
- Ran the query on CentOS box and it exhibited the same behavior
- Ran the query on 8.4 Successfully
In my opinion, it seems fairly abnormal for PostgreSQL to use so much memory, and is alerting to the problem. We just can't find it!
Any comments, thoughts, and ideas are appreciated!
- Ubuntu 11.04 64 bit
- Intel i5-2400
- 4 GB ram
- PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit