Please help me speed up the following query. It used to run in 2-5 sec., but now it takes 2-3 mins!
I ran VACUUM FULL ANALYZE and REINDEX.
SELECT * FROM media m
WHERE m.mediatype = (SELECT objectid FROM mediatype WHERE medianame='Audio')
AND EXISTS
(SELECT * FROM
(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' ) AS a1
WHERE a1.mediaid = m.objectid
LIMIT 1)
ORDER BY medianame ASC, status DESC
Basically it tries to find all Audios that are either explicitly attached to the given activity, or attached to the given activity via a many-to-many relationship intsetmedia which links records in table Interaction, Set, and Media.
I attached the output of EXPLAIN and schemas and indexes on the tables involved. Most of the fields are not relevant to the query, but I listed them anyways. I discarded trigger information, though.
Thanks for your help.
Oleg
*************************************
This email may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Any review, copying, printing, disclosure or other use is prohibited.
We reserve the right to monitor email sent through our network.
*************************************