Re: speeding up subqueries

Phil Glatz <phil@glatz.com> writes:
> I'm having difficulties getting a subselect to perform well.  I've used
> EXPLAIN to try to understand the problem,

And?

> I don't understand what is going on here, since the inner subquery runs
> very fast, and the entire query also runs fast if I substitute the list of
> returned values instead of a subquery.

Yeah, but the inner query has to be done over for every row of the
outer.

You might try converting to a joinable subselect:

SELECT COUNT(*) FROM
  quiksearch q
join
  (select distinct org_id FROM org_resource_type WHERE resource=12) ss
using (org_id)
WHERE q.resource_status_id=1;

This would probably be a win if the DISTINCT processing is not too
terribly expensive, which'd depend on the number of rows selected from
org_resource_type ... but for a few thousand rows as you illustrated,
it shouldn't be bad.

Experimenting on this with dummy tables, I get a hash join plan, which
looks pretty reasonable.

            regards, tom lane