From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oleg Lebedev <oleg.lebedev@waterford.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow query
Date: Mon, 24 Mar 2003 15:48:10 -0500
Message-ID: <20170.1048538890@sss.pgh.pa.us> (raw)
In-Reply-To: <993DBE5B4D02194382EC8DF8554A5273113E5A@postoffice.waterford.org>
References: <993DBE5B4D02194382EC8DF8554A5273113E5A@postoffice.waterford.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, oleg.lebedev@waterford.org
Subject: Re: Slow query
In-Reply-To: <20170.1048538890@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox