SOLVED
On Tue, Dec 20, 2011 at 3:46 PM, Tom Lane - tgl@sss.pgh.pa.us
<+nabble+miller_2555+c5a65c2e1a.tgl#sss.pgh.pa.us@spamgourmet.com>
wrote:
> nabble.30.miller_2555@spamgourmet.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