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

Re: Performance problem with correlated sub-query

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: "Howard, Steven (US - Tulsa)" <sthoward(at)deloitte(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Performance problem with correlated sub-query
Date: 2004-04-29 14:00:31
Message-ID: 40910A7F.7040803@mascari.com (view raw or flat)
Thread:
Lists: pgsql-general
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





In response to

pgsql-general by date

Next:From: Seth WilliamsDate: 2004-04-29 14:05:08
Subject: Function to get hostname
Previous:From: Mike MascariDate: 2004-04-29 13:50:34
Subject: Re: Postgre and Web Request

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