Is there a way to speed up a DISTINCT query?
As suggested, an index on (site, sn) could speed this up, especially if the table is well-vacuumed so that it benefits from index-only scans.

If that is not enough of a speed up, then there is a way to use this index in a "skip scan" or "Loose Index Scan" to speed up queries where the number of distinct values is much lower than the size of the index. Unfortunately PostgreSQL's planner does not automatically detect opportunities for this, but you can force it yourself by writing a recursive common table expression.  You can find a discussion of this technique on the PostgreSQL wiki, although you will need to adapt it to your parameterized version.

The resulting queries are quite ugly, so you might want to wrap them into a view.  Or in this case, since it is parameterized, into a set-returning function.  Something like this works for me:

CREATE FUNCTION public.foobar(text) RETURNS SETOF text
    LANGUAGE sql
    AS $_$ with recursive r as (
  (select sn from t where site = $1 order by sn limit 1)
   union all
  SELECT (SELECT t.sn FROM t WHERE site=$1 and t.sn &gt; r.sn ORDER BY sn LIMIT 1) from r where sn is not null
)
select * from r where r.sn is not null $_$;


Setting up the table and index with:

create table t as select 
    floor(random()*1.2)::int::varchar as site, 
    (-floor(50*log(random()))::int)::varchar as sn 
from generate_series(1,10000000);

create index on t (site ,sn);