"Michael T. Halligan" <michael@echo.com> writes:
> The query sorts through about 80k rows.. here's the query
> --------------------------------------------------
> SELECT count(*) FROM (
> SELECT DISTINCT song_id FROM ssa_candidate WHERE
> style_id IN (
> SELECT style_id FROM station_subgenre WHERE
> station_id = 48
> )
> ) AS X;
The standard advice for speeding up WHERE ... IN queries is to convert
them to WHERE ... EXISTS. However, assuming that there are not very
many style_ids for any one station_id in station_subgenre, this probably
won't help much. What I'd try is converting it to a straight join:
SELECT count(DISTINCT song_id) FROM ssa_candidate, station_subgenre
WHERE
ssa_candidate.style_id = station_subgenre.style_id AND
station_id = 48;
Normally this would not do what you want, since you could end up with
multiple joined rows for any one ssa_candidate row, but given that
you're going to do a DISTINCT that doesn't really matter. Better to
let the thing use a more efficient join method and just throw away the
extra rows in the DISTINCT step. Or that's my theory anyway; let us
know how well it works.
BTW, are the row estimates in the EXPLAIN output anywhere close to
reality?
regards, tom lane