Skip site navigation (1) Skip section navigation (2)

Slow query

From: Oleg Lebedev <oleg(dot)lebedev(at)waterford(dot)org>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Slow query
Date: 2003-03-24 17:48:52
Message-ID: 993DBE5B4D02194382EC8DF8554A5273113E5A@postoffice.waterford.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

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

Attachment: schemas.txt
Description: text/text (4.9 KB)
Attachment: plan.txt
Description: text/text (1.3 KB)

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2003-03-24 18:54:49
Subject: Re: Slow query
Previous:From: Josh BerkusDate: 2003-03-23 21:55:02
Subject: Re: Slow update of indexed column with many nulls

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group