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

Re: Performance problem with correlated sub-query

From: Paul Thomas <paul(at)tmsl(dot)demon(dot)co(dot)uk>
To: sthoward(at)DELOITTE(dot)com
Cc: "pgsql-general (at) postgresql (dot) org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Performance problem with correlated sub-query
Date: 2004-04-29 14:19:55
Message-ID: 20040429151955.C17144@bacon (view raw or flat)
Thread:
Lists: pgsql-general
On 29/04/2004 14:34 "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.

7.0? That's positively ancient!
> 
> When the user requests the first page, he gets a list of all the servers
> with maintenance records in the database, and a drop down list of all
> the dates of maintenance records. If the user chooses a date first, then
> the app uses a prepared statement with the date contained in a
> parameter, and this executes very quickly - no problems.
> 
> 
> 
> 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);
> 
> 
> 
> 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.

PG doesn't use indexes for things like count(), max, min()...

You can avoid using max() by something like

select my_date from my_table order by my_date desc limit 1;

which will use the index.

> 
> Is there a way to improve the performance on this query?

In addition to the above, I'd strongly recommend upgrading to 7.4 to take
advantage of the last ~4 years of continuous improvements.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+


In response to

pgsql-general by date

Next:From: Howard, Steven (US - Tulsa)Date: 2004-04-29 14:27:41
Subject: Re: Performance problem with correlated sub-query
Previous:From: Shridhar DaithankarDate: 2004-04-29 14:12:46
Subject: Re: Performance problem with correlated sub-query

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