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 QAA49127 for ; Fri, 17 Dec 1999 16:46:48 -0500 (EST) (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 QAA10360; Fri, 17 Dec 1999 16:45:51 -0500 (EST) To: "Mitch Vincent" cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Sub-select speed. In-reply-to: Your message of Fri, 17 Dec 1999 13:50:06 -0500 <018301bf48bf$8a068620$0300000a@doot.org> Date: Fri, 17 Dec 1999 16:45:51 -0500 Message-ID: <10357.945467151@sss.pgh.pa.us> From: Tom Lane "Mitch Vincent" writes: > select * from applicants as a where a.status = 'A' and a.app_id in(select > b.app_id from resume_search as b where a.app_id=b.app_id and b.user_id=291) WHERE ... IN (sub-select) is a very inefficient substitute for a plain join query. Try something like select a.* from applicants as a, resume_search as b where a.status = 'A' and a.app_id = b.app_id and b.user_id = 291; Providing indexes on one or both app_id fields might help. Also, don't forget to do a VACUUM every so often to make sure the planner has useful statistics about the sizes of the tables. There has been some talk of automatically rewriting queries to eliminate unnecessary sub-selects, but I don't foresee it getting done for a while yet. > insert into users_download_app (user_id,app_id) select app_id from > applicants where status = 'A' and app_id in(select b.app_id from > resume_search as b where app_id=b.app_id and b.user_id=291) limit 200 BTW, there's a bug in current sources: LIMIT is ignored by INSERT ... SELECT. I think it works OK to do SELECT INTO ... LIMIT, however, and then you could insert into the final destination table from the INTO temp table. regards, tom lane