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

Re: Slow query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Oleg Lebedev <oleg(dot)lebedev(at)waterford(dot)org>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Slow query
Date: 2003-03-24 20:48:10
Message-ID: 20170.1048538890@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Oleg Lebedev <oleg(dot)lebedev(at)waterford(dot)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


In response to

  • Slow query at 2003-03-24 17:48:52 from Oleg Lebedev

pgsql-performance by date

Next:From: Oleg LebedevDate: 2003-03-24 21:46:09
Subject: Re: Slow query
Previous:From: Oleg LebedevDate: 2003-03-24 20:28:47
Subject: Re: Slow query

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