"Mitch Vincent" <mitch@venux.net> 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
