Re: Tuning questions..

"Michael T. Halligan" <michael(at)echo(dot)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