Re: OOM-killer issue with a specific query SOLVED

SOLVED
On Tue, Dec 20, 2011 at 3:46 PM, Tom Lane - tgl(at)sss(dot)pgh(dot)pa(dot)us
<+nabble+miller_2555+c5a65c2e1a(dot)tgl#sss(dot)pgh(dot)pa(dot)us(at)spamgourmet(dot)com>
wrote:
> 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.
>
I've updated the query to use NOT EXISTS, which does produce a vastly
more efficient plan and barely moves memory consumption when running.
Since NULLS are not permitted in the bigint columns, this works really
well. Thanks Tom - this has saved me a lot of head bashing!

> 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.
>
ha - true...I've been pretty remiss in updating development
environment system components - might be a project for the holidays :)

Thanks again