X-Original-To: pgsql-performance@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 40E65475F5F
	for <pgsql-performance@postgresql.org>;
	Mon, 24 Mar 2003 15:48:41 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 3CE11474E44
	for <pgsql-performance@postgresql.org>;
	Mon, 24 Mar 2003 15:48:10 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.8/8.12.8) with ESMTP id h2OKmAub020171;
	Mon, 24 Mar 2003 15:48:11 -0500 (EST)
To: Oleg Lebedev <oleg.lebedev@waterford.org>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow query 
In-reply-to: <993DBE5B4D02194382EC8DF8554A5273113E5A@postoffice.waterford.org>
References: <993DBE5B4D02194382EC8DF8554A5273113E5A@postoffice.waterford.org>
Comments: In-reply-to Oleg Lebedev <oleg.lebedev@waterford.org>
	message dated "Mon, 24 Mar 2003 10:48:52 -0700"
Date: Mon, 24 Mar 2003 15:48:10 -0500
Message-ID: <20170.1048538890@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Spam-Status: No, hits=-27.9 required=5.0
	tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,REFERENCES,
	REPLY_WITH_QUOTES,UPPERCASE_25_50 autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200303/167
X-Sequence-Number: 1465

Oleg Lebedev <oleg.lebedev@waterford.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