Message-ID: <50A51154.5060307@nasa.gov>
Date: Thu, 15 Nov 2012 10:59:16 -0500
From: "Maria L. Wilson" <Maria.L.Wilson-1@nasa.gov>
Reply-To: Maria.L.Wilson-1@nasa.gov
Organization: SSAI
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6;
	rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: slow query on postgres 8.4
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

Can someone shed some light on the following query.....
any help would certainly be appreciated!

thanks -

*****
Maria Wilson
Nasa/Langley Research Center
Hampton, Virginia
m.l.wilson@nasa.gov
*****

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


"Limit  (cost=5325840.21..5325840.21 rows=1 width=268) (actual 
time=20418.800..20422.577 rows=1000 loops=1)"
"  ->  Nested Loop  (cost=5323597.90..5325840.21 rows=200 width=268) 
(actual time=20406.888..20422.265 rows=4000 loops=1)"
"        ->  HashAggregate  (cost=5323597.90..5323599.90 rows=200 
width=8) (actual time=20406.867..20407.927 rows=4000 loops=1)"
"              ->  GroupAggregate  (cost=4701622.10..5090733.69 
rows=18629137 width=44) (actual time=20359.752..20389.387 rows=58552 
loops=1)"
"                    ->  Sort  (cost=4701622.10..4753704.56 
rows=20832984 width=44) (actual time=20359.746..20367.125 rows=59325 
loops=1)"
"                          Sort Key: b.aipguid"
"                          Sort Method:  quicksort  Memory: 6171kB"
"                          ->  Seq Scan on aipmodificationevent b  
(cost=23.24..1528265.92 rows=20832984 width=44) (actual 
time=1647.075..20188.844 rows=59325 loops=1)"
"                                Filter: ((hashed SubPlan 1) OR (hashed 
SubPlan 2))"
"                                SubPlan 1"
"                                  ->  Index Scan using 
wk_eventtimestamp_idx1 on workflowprocessingevent c (cost=0.00..11.62 
rows=1 width=37) (actual time=0.053..40.741 rows=35945 loops=1)"
"                                        Index Cond: ((eventtimestamp >= 
'2012-11-10 00:00:00'::timestamp without time zone) AND (eventtimestamp 
< '2012-11-11 00:00:00'::timestamp without time zone))"
"                                        Filter: ((discriminator)::text 
= 'WorkflowCompleted'::text)"
"                                SubPlan 2"
"                                  ->  Index Scan using 
wk_eventtimestamp_idx1 on workflowprocessingevent c (cost=0.00..11.62 
rows=1 width=37) (actual time=0.035..31.820 rows=35945 loops=1)"
"                                        Index Cond: ((eventtimestamp >= 
'2012-11-10 00:00:00'::timestamp without time zone) AND (eventtimestamp 
< '2012-11-11 00:00:00'::timestamp without time zone))"
"                                        Filter: ((discriminator)::text 
= 'WorkflowCompleted'::text)"
"        ->  Index Scan using aipmodificationevent_pkey on 
aipmodificationevent a  (cost=0.00..11.19 rows=1 width=268) (actual 
time=0.003..0.003 rows=1 loops=4000)"
"              Index Cond: (a.id = (max(b.id)))"
"Total runtime: 20422.761 ms"