pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp on slow query using postgres 8.4
2+ messages / 2 participants
[nested] [flat]
* help on slow query using postgres 8.4
@ 2012-11-15 15:03 Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
0 siblings, 0 replies; 2+ messages in thread
From: Maria L. Wilson @ 2012-11-15 15:03 UTC (permalink / raw)
To: pgsql-performance
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"
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: help on slow query using postgres 8.4
@ 2012-11-20 13:06 Kevin Grittner <kgrittn@mail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Kevin Grittner @ 2012-11-20 13:06 UTC (permalink / raw)
To: Maria.L.Wilson-1@nasa.gov; pgsql-performance
Maria L. Wilson wrote:
> Can someone shed some light on the following query.....
> any help would certainly be appreciated!
The query text and EXPLAIN ANALYZE output are a good start, but a lot
of other information is needed to really understand the issue.
http://wiki.postgresql.org/wiki/SlowQueryQuestions
The EXPLAIN ANALYZE output will be easier to read as an attachment --
or even better, post it to:
http://explain.depesz.com/
-Kevin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-11-20 13:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-15 15:03 help on slow query using postgres 8.4 Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
2012-11-20 13:06 Re: help on slow query using postgres 8.4 Kevin Grittner <kgrittn@mail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox