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
