Re: speeding up subqueries

Phil Glatz <phil(at)glatz(dot)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