Re: Slow query

Oleg,

My guess is that the query runs slow because by adding
data you exceeded what your database can do in memory
and you need to do some kind of disk sort.

How about rewriting your query without the UNION and
the EXISTS to something like

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

Regards,
Nikolaus Dilger

On Mon, 24 Mar 2003, Oleg Lebedev wrote:


Message



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.

*************************************