Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA16137 for ; Mon, 10 Apr 2000 19:26:05 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id TAA24284; Mon, 10 Apr 2000 19:25:35 -0400 (EDT) To: "Mitch Vincent" cc: pgsql-sql@postgresql.org Subject: Re: Speedy query help.. In-reply-to: <004901bfa32c$63c051e0$4100000a@venux.net> References: <17328.955396227@sss.pgh.pa.us> <004901bfa32c$63c051e0$4100000a@venux.net> Comments: In-reply-to "Mitch Vincent" message dated "Mon, 10 Apr 2000 16:36:03 -0400" Date: Mon, 10 Apr 2000 19:25:35 -0400 Message-ID: <24281.955409135@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200004/98 "Mitch Vincent" 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