New Query:
SELECT * FROM media m 
JOIN 
((SELECT objectid AS mediaid 
FROM media 
WHERE activity='347667') 
UNION 
(SELECT ism.media AS mediaid 
FROM intsetmedia ism, set s 
WHERE ism.set = s.objectid 
AND s.activity='347667' )) a1 
ON 
m.mediatype = (SELECT objectid FROM mediatype WHERE medianame='Audio') 
AND m.objectid=mediaid 
ORDER BY medianame ASC, status DESC 

 
QUERY PLAN 
 Sort (cost=1831.47..1831.47 rows=1 width=225) (actual time=19.56..19.58 rows=27 loops=1) 
  Sort Key: m.medianame, m.status 
  InitPlan 
  -> Seq Scan on mediatype (cost=0.00..1.29 rows=1 width=8) (actual time=0.10..0.12 rows=1 loops=1) 
  Filter: (medianame = 'Audio'::character varying) 
  -> Nested Loop (cost=1702.97..1831.46 rows=1 width=225) (actual time=16.44..18.93 rows=27 loops=1) 
  -> Subquery Scan a1 (cost=1702.97..1704.19 rows=24 width=24) (actual time=16.10..16.72 rows=58 loops=1) 
  -> Unique (cost=1702.97..1704.19 rows=24 width=24) (actual time=16.08..16.56 rows=58 loops=1) 
  -> Sort (cost=1702.97..1703.58 rows=243 width=24) (actual time=16.08..16.25 rows=264 loops=1) 
  Sort Key: mediaid 
  -> Append (cost=0.00..1693.32 rows=243 width=24) (actual time=0.07..9.11 rows=264 loops=1) 
  -> Subquery Scan "*SELECT* 1" (cost=0.00..505.76 rows=169 width=8) (actual time=0.06..0.65 rows=55 loops=1) 
  -> Index Scan using media_act_index on media (cost=0.00..505.76 rows=169 width=8) (actual time=0.06..0.53 rows=55 loops=1) 
  Index Cond: (activity = 347667::bigint) 
  -> Subquery Scan "*SELECT* 2" (cost=0.00..1187.57 rows=75 width=24) (actual time=0.11..8.11 rows=209 loops=1) 
  -> Nested Loop (cost=0.00..1187.57 rows=75 width=24) (actual time=0.10..7.68 rows=209 loops=1) 
  -> Index Scan using set_act_index on "set" s (cost=0.00..24.23 rows=6 width=8) (actual time=0.05..0.06 rows=2 loops=1) 
  Index Cond: (activity = 347667::bigint) 
  -> Index Scan using intsetmedia_set_index on intsetmedia ism (cost=0.00..203.42 rows=68 width=16) (actual time=0.04..3.56 rows=104 loops=2) 
  Index Cond: (ism."set" = "outer".objectid) 
  -> Index Scan using media_pkey on media m (cost=0.00..5.22 rows=1 width=217) (actual time=0.03..0.03 rows=0 loops=58) 
  Index Cond: (m.objectid = "outer".mediaid) 
  Filter: (mediatype = $0) 
 Total runtime: 20.36 msec