Re: OOM-killer issue with a specific query 9 of 20)

nabble(dot)30(dot)miller_2555(at)spamgourmet(dot)com writes:
> I've run EXPLAIN on the query, but AFAICS the query plan does not
> appear significantly different than the abridged version for this
> particular query (output attached below).

I think what's happening is that you've got the hashed NOT IN being
pushed down separately to each of the 180 child tables, so each of those
hashtables thinks it can use work_mem (32MB), which means you're pushing
6GB of memory usage before accounting for anything else.

NOT IN is really hard to optimize because of its weird behavior for
nulls, so the planner doesn't have much of any intelligence about it.
I'd suggest seeing if you can transform it to a NOT EXISTS, if you
don't have any nulls in the bigint columns or don't really want the
spec-mandated behavior for them anyway. A quick check suggests that 9.0
should give you a vastly better plan from a NOT EXISTS.

Another suggestion is that you ought to be running something newer than
9.0.0; you're missing over a year's worth of bug fixes (some of which
were memory leaks...). If you are going to pick a PG version to sit on
and not bother to update, a dot-zero release is about your worst
possible choice; it will always have more bugs than a more mature
release series. With my red fedora on, I'd also mutter that F13 is well
past its use-by date.

regards, tom lane