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

Re: Performance problem with correlated sub-query

From: Shridhar Daithankar <shridhar(at)frodo(dot)hserus(dot)net>
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:12:46
Message-ID: 40910D5E.1030204@frodo.hserus.net (view raw or flat)
Thread:
Lists: pgsql-general
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.
> 
> 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.

Few basic checks..
- What does explain analyze says for the slow query?
- Have you vacuumed and analyzed recently?
- Have you done basic optimisations from default state? Check 
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html and 
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html

And 7.0 is way too old. If you can afford to upgrade, upgrade to 7.4.2.

  HTH

  Shridhar


In response to

pgsql-general by date

Next:From: Paul ThomasDate: 2004-04-29 14:19:55
Subject: Re: Performance problem with correlated sub-query
Previous:From: Stephan SzaboDate: 2004-04-29 14:07:09
Subject: Re: Performance problem with correlated sub-query

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