Re: Performance problem with correlated sub-query

On Thu, 29 Apr 2004, Howard, Steven (US - Tulsa) wrote:

> I have created a web app that stores and displays all the messages from
> my database maintenance jobs that run each night. The web app uses Java
> servlets and has PostgreSQL 7.0 as the back end.

Step 1 is upgrade. ;)

> However, if the web page user does not choose a date, then the app uses
> a correlated sub-query to grab only the current (latest) day's
> maintenance records. The query that is executed is:
>
> 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);

This is likely to be running the subquery once for each row in messages,
and probably not going to use an index in the inner either. The former
might be optimized by recent versions.

Changing the inner query to something like:
(select date_of_msg from messages i where i.servername=o.servername
order by date_of_msg desc limit 1)

or changing it to use a subselect in from (something like):
from messages o, (select servername, max(date_of_msg) from messages) i
where o.servername=i.servername

might both help, but I'm not sure either will work on 7.0.

> 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.

You might wish to play around with changing the indexes and the order of
the columns in the multicolumn index as well.