From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mitch Vincent <mitch@venux.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Speedy query help..
Date: Mon, 10 Apr 2000 19:25:35 -0400
Message-ID: <24281.955409135@sss.pgh.pa.us> (raw)
In-Reply-To: <004901bfa32c$63c051e0$4100000a@venux.net>
References: <NEBBLMLPILFNHKNECKIFIENOCAAA.robertn@eori.net.pl>
<17328.955396227@sss.pgh.pa.us>
<004901bfa32c$63c051e0$4100000a@venux.net>
"Mitch Vincent" <mitch@venux.net> writes:
> Any ideas on how I might speed this up? I know sub-selects are seriously
> slow, I'm not sure what else can be done though.
> DELETE from applicants_states WHERE app_id IN (SELECT s.app_id FROM
> applicants_states AS s, applicants AS a WHERE s.app_id=a.app_id AND
> (a.created + '90 days') < 'now' AND a.resubmitted < '10-03-1999')
I believe you'd get the same result from
DELETE FROM applicants_states
WHERE app_id = applicants.app_id AND
(applicants.created + '90 days') < 'now' AND
applicants.resubmitted < '10-03-1999';
This is not SQL-standard; doing an implicit join when you mention
another table in WHERE is a leftover from Berkeley Postquel. But
it solves this sort of problem rather handily.
If you want to stick to portable SQL, I'd at least suggest getting rid
of the unnecessary join in the subselect; wouldn't
DELETE from applicants_states WHERE app_id IN (SELECT app_id
FROM applicants
WHERE (created + '90 days') < 'now' AND resubmitted < '10-03-1999');
produce the same results?
Also, 7.0 does uncorrelated subselects (like this one) somewhat
faster than prior releases, so just upgrading might solve the problem
for you.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, mitch@venux.net
Subject: Re: Speedy query help..
In-Reply-To: <24281.955409135@sss.pgh.pa.us>
References: <NEBBLMLPILFNHKNECKIFIENOCAAA.robertn@eori.net.pl> <17328.955396227@sss.pgh.pa.us> <004901bfa32c$63c051e0$4100000a@venux.net> <24281.955409135@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