PostgreSQL memory spike upgrade from 8.2 to 9.1 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
9.1 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!

Machine Info:


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
In a case like this where nothing significant has changed I think you are best off bringing this up on the PostgreSQL email lists.  Not only will you get help with your issue in terms of workarounds but if there is a planner bug somewhere the development team will be given notice so they can fix it.

This may be one of those cases where the level of help you can expect there is far better than you can expect here.