Re: [GENERAL] Performance problem with correlated sub-query

Thu, 29 Apr 2004 07:16:27 -0700 

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 – 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’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

Reply via email to