Re: Performance problem with correlated sub-query

Mike Mascari <mascarm@mascari.com> · Thu, 29 Apr 2004 10:00:31 -0400

Howard, Steven (US - Tulsa) wrote:

select servername, databasename, message from messages o where
o.date_of_msg = (select max(date_of_msg) from messages i where
i.servername = o.servername);

And this is a dog. It takes 15 &#8211; 20 minutes to execute the
query (there are about 200,000 rows in the table). I have an
index on (servername, date_of_msg), but it doesn&#8217;t seem to
be used in this query.

Just off the top of my head:

SELECT servername, databasename, message
FROM messages o
WHERE o.date_of_msg = (
 SELECT date_of_msg
 FROM messages i
 WHERE i.servername = o.servername
 ORDER BY date_of_msg
 LIMIT 1
);

HTH,

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faqs/FAQ.html