FW: slow query on postgres 8.4

> explain analyze
> select a.ID, a.provider, a.hostname, a.username, a.eventTimeStamp, a.AIPGUID, a.submissionGUID, a.parentSubmissionGUID, a.sizeArchived, a.addedContentString, a.addedContentSizesString, a.removedContentString, a.removedContentSizesString, a.modifiedContentString, a.modifiedContentSizesString, a.DISCRIMINATOR
> from AIPModificationEvent a
> where a.ID in (select MAX(b.ID) from AIPModificationEvent b where b.parentSubmissionGUID
> in
> (select c.GUID from WorkflowProcessingEvent c where c.DISCRIMINATOR='WorkflowCompleted'
> and c.eventTimeStamp >= '2012-11-10 00:00:00' and c.eventTimeStamp < '2012-11-11 00:00:00')
> or b.submissionGUID in
> (select c.GUID from WorkflowProcessingEvent c
> where c.DISCRIMINATOR='WorkflowCompleted' and c.eventTimeStamp >= '2012-11-10 00:00:00' and c.eventTimeStamp <
> '2012-11-11 00:00:00')
> group by b.AIPGUID)
> limit 1000 offset 3000

Hi Maria,

It appears to be doing a sort so that it can carry out the group by clause but the group by doesn't appear to be necessary as you're selecting the max(b.ID) after doing the group by.
If you omit the group by then it will return more rows in that part of the query but the MAX(b.ID) will return 1 value regardless.

Regards,

Russell Keane.

Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.uk
The information in this internet email is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is not authorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of INPS or any of its affiliates. If you are not the intended recipient please contact is(dot)helpdesk(at)inps(dot)co(dot)uk