Re: Tuning questions..

"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