3

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
Improve this question
11
  • Could you show the SELECT as well?
    – dezso
    May 22, 2012 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.
    – user1822
    May 22, 2012 at 19:51
  • @dezso : Unfortunately, I cannot show the SELECT query :(
    – bjester
    May 22, 2012 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, 2012 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, 2012 at 22:42

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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