Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Replicated config settings for 8.2 to 9.1
  2. 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)
  3. Broken the query down by removing joins. When below any 10 joins, the query will complete
  4. Examined the joining tables for irregularities
  5. Tested the query on different machines running 9.1
  6. Reviewed Postgres config documentation and changelog
  7. Ran VACUUM and ANALYZE on the db
  8. Rebooted the machine
  9. Updated kernel SHMMAX when postgres wouldn't start after reboot
  10. Ran the query on CentOS box and it exhibited the same behavior
  11. 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
share|improve this question
    
Could you show the SELECT as well? –  dezso May 22 '12 at 18:41
    
A shot in the dark: maybe it's the hashing that uses so much memory. You can try disabling hashjoins (set enable_hashjoin=false) for a test and see what happens then. But this sure sounds like a problem you should report on the mailing list. I'm sure the Postgres developers would like to hear about this - as that does seem like a regression. –  a_horse_with_no_name May 22 '12 at 19:51
    
@dezso : Unfortunately, I cannot show the SELECT query :( –  bjester May 22 '12 at 20:28
    
@a_horse_with_no_name : I will try that, I remember reading something about how an outer join operates differently in a later version then 8.2 (it said something about hashing). –  bjester May 22 '12 at 20:31
    
@a_horse_with_no_name Changing enable_hashjoins = off worked, the query ran successfully. I now have a lead to research; I will post my results! Thanks! –  bjester May 22 '12 at 22:42
show 6 more comments

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.