Re: OOM-killer issue with a specific query SOLVED

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux