Re: Performance problem with correlated sub-query
От | Paul Thomas |
---|---|
Тема | Re: Performance problem with correlated sub-query |
Дата | |
Msg-id | 20040429151955.C17144@bacon обсуждение исходный текст |
Ответ на | Performance problem with correlated sub-query ("Howard, Steven (US - Tulsa)" <sthoward@DELOITTE.com>) |
Список | 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 | +------------------------------+---------------------------------------------+
В списке pgsql-general по дате отправления: