You are right. I rewrote the query using JOINs and it increased
performance from 123 sec. to 20msec. I betcha I screwed smth up, but I
list the rewritten query below anyways. I also attached the new plan.
Thank you.
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
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, March 24, 2003 3:09 PM
To: Oleg Lebedev
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Slow query
Oleg Lebedev <oleg.lebedev@waterford.org> writes:
> I just ran the query you sent me and attached the output of EXPLAIN
> ANALYZE as TOMs_plan.txt It did not speed up the query significantly.
Nope. I was hoping to see a faster-start plan, but given the number of
rows involved I guess it won't change its mind. You're going to have to
think about a more intelligent approach, rather than minor tweaks.
One question: since objectid is evidently a primary key, why are you
doing a subselect for the first part? Wouldn't it give the same result
just to say "m.activity = '347667'" in the top-level WHERE?
As for the second part, I think you'll have to try to rewrite it as a
join with the media table.
regards, tom lane
*************************************
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.
*************************************