From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: sqllist <pgsql-sql@postgresql.org>
Subject: Re: Query Problem
Date: Thu, 26 Oct 2000 11:16:39 -0400
Message-ID: <5722.972573399@sss.pgh.pa.us> (raw)
In-Reply-To: <39F78FE7.DA915769@agliodbs.com>
References: <39F78FE7.DA915769@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> Thus, I need to select:
> SELECT Data FROM Table A
> WHERE CaseID NOT IN (
> SELECT CaseID FROM Table_B, Table_C
> WHERE Table_B.GroupID = TableC.GroupID
> AND TableC.AccountID = 11)
> The problem is, since Table_B and Table_C are large (10,000 records +)
> this exclusion query takes several *minutes* to run.
I don't think there is any good way to make this fast in current
sources. A partial workaround is to use a temp table:
SELECT CaseID INTO TEMP TABLE mycaseids FROM Table_B, Table_C
WHERE Table_B.GroupID = TableC.GroupID
AND TableC.AccountID = 11;
CREATE INDEX mycaseids_idx ON mycaseids(caseid); -- critical!
SELECT Data FROM TableA upper
WHERE NOT EXISTS (select 1 from mycaseids where caseid = upper.caseid);
You'd need to check with EXPLAIN, but the EXISTS subplan should make
use of the index to probe the temp table, so you get one index lookup
per outer tuple. Better than a complete scan of the subselect outputs,
which is what you'll get with the NOT IN style.
In 7.1 it'll be possible to do this with an outer join, which should
be a lot quicker:
SELECT Data FROM TableA LEFT JOIN
(SELECT CaseID FROM Table_B, Table_C
WHERE Table_B.GroupID = TableC.GroupID
AND TableC.AccountID = 11) subselect
ON (tablea.caseid = subselect.caseid)
WHERE subselect.caseid IS NULL;
ie, do the outer join and then discard the successfully-matched rows.
Further down the pike, we have plans to make the system smart enough to
transform IN and NOT IN constructs into join-like queries automatically.
Right now, though, they're best rewritten into something else when
performance is important.
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: Query Problem
In-Reply-To: <5722.972573399@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