From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steinar H. Gunderson <sgunderson@bigfoot.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query take 101 minutes, help, please
Date: Wed, 07 Sep 2005 13:57:36 -0400
Message-ID: <28614.1126115856@sss.pgh.pa.us> (raw)
In-Reply-To: <20050907165754.GA12319@uio.no>
References: <d20db5e605090709227c2c3596@mail.gmail.com>
<20050907165754.GA12319@uio.no>
"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
> (I'm not sure how optimized UNION inside an IN/NOT IN is.)
NOT IN is pretty nonoptimal, period. It'd help a lot to boost work_mem
to the point where the planner figures it can use a hashtable (look for
EXPLAIN to say "hashed subplan" rather than just "subplan"). Of course,
if there's enough stuff in the UNION that that drives you into swapping,
it's gonna be painful anyway.
Using UNION ALL instead of UNION might save a few cycles too.
If you're willing to rewrite the query wholesale, you could try the old
trick of a LEFT JOIN where you discard rows for which there's a match,
ie, the righthand join value isn't NULL.
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, sgunderson@bigfoot.com
Subject: Re: Query take 101 minutes, help, please
In-Reply-To: <28614.1126115856@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