So I'm having a problem with an "insane" query that's the result of
joining several views. This query seems to consume 10's of megabytes of
memory a second, until either the query completes or the Linux OOM
killer takes it out. Completing a small subset of the data (5%) takes
almost 35% of the memory of a 4G box.
I would post an explain, but it's over 250 lines long (I told you the
query was insane- abuse of views, woo hoo!), and I haven't been able to
reproduce the problem with a simpler query. This is on Postgres 8.1.6.
Fortunately this was on a development box, so no harm done, but it still
worries me.
The plan does, however, contain many dozens of sorts and dozens more of
hashes, so of course my first thought was my generous workmem (32768) or
temp_buffers (48000) settings. Reducing these to 128 and 100
respectively did not change the memory utilization profile.
So, I have a couple of questions:
1) What can I look at to see where the memory is going? Or can someone
just tell me?
2) Is there any way I can prevent someone from crashing production in
the same way (production is 8.1.10)?
Thanks.
Brian
