Re: Slow query

Oleg Lebedev <oleg(dot)lebedev(at)waterford(dot)org> writes:
> SELECT * FROM media m
> WHERE m.mediatype =3D (SELECT objectid FROM mediatype WHERE
> medianame=3D'Audio')=20
> AND EXISTS=20
> (SELECT * FROM=20
> (SELECT objectid AS mediaid=20
> FROM media=20
> WHERE activity=3D'347667'=20
> UNION=20
> SELECT ism.media AS mediaid=20
> FROM intsetmedia ism, set s=20
> WHERE ism.set =3D s.objectid=20
> AND s.activity=3D'347667' ) AS a1=20
> WHERE a1.mediaid =3D m.objectid=20
> LIMIT 1)=20
> ORDER BY medianame ASC, status DESC=20

Well, one observation is that the LIMIT clause is useless and probably
counterproductive; EXISTS takes only one row from the subselect anyway.
Another is that the UNION is doing it the hard way; UNION implies doing
a duplicate-elimination step, which you don't need here. UNION ALL
would be a little quicker. But what I would do is split it into two
EXISTS:

SELECT * FROM media m
WHERE m.mediatype = (SELECT objectid FROM mediatype WHERE
medianame='Audio')
AND ( EXISTS(SELECT 1
FROM media
WHERE activity='347667'
AND objectid = m.objectid)
OR EXISTS(SELECT 1
FROM intsetmedia ism, set s
WHERE ism.set = s.objectid
AND s.activity='347667'
AND ism.media = m.objectid))
ORDER BY medianame ASC, status DESC

regards, tom lane