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