From: Tom Lane <tgl@sss.pgh.pa.us>
To: nabble.30.miller_2555@spamgourmet.com
Cc: pgsql-performance@postgresql.org
Subject: Re: OOM-killer issue with a specific query 9 of 20)
Date: Tue, 20 Dec 2011 15:46:46 -0500
Message-ID: <16161.1324414006@sss.pgh.pa.us> (raw)
In-Reply-To: <CABLpH8zUZroESqpe+fiJ4pvtEgm5sHehUHRJkDGbRRp9Fk9hTw@mail.gmail.com>
References: <CABLpH8y=QiHwshW6ss66_62_ddxQFQ=f9MtKv8GqThi-h90xbA@mail.gmail.com>
<CAOR=d=3ja67YHxH50Aec+SCgbsB6T7wJ2Ov-D04gbmcRT4ifng@mail.gmail.com>
<CABLpH8zUZroESqpe+fiJ4pvtEgm5sHehUHRJkDGbRRp9Fk9hTw@mail.gmail.com>
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.
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, nabble.30.miller_2555@spamgourmet.com
Subject: Re: OOM-killer issue with a specific query 9 of 20)
In-Reply-To: <16161.1324414006@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox