help on slow query using postgres 8.4

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxx
*****

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"



--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux