pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow query on postgres 8.4
2+ messages / 2 participants
[nested] [flat]
* slow query on postgres 8.4
@ 2012-11-15 15:59 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:59 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
* FW: slow query on postgres 8.4
@ 2012-11-20 11:16 Russell Keane <Russell.Keane@inps.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: Russell Keane @ 2012-11-20 11:16 UTC (permalink / raw)
To: pgsql-performance
> 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.helpdesk@inps.co.uk
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-11-20 11:16 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:59 slow query on postgres 8.4 Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
2012-11-20 11:16 FW: slow query on postgres 8.4 Russell Keane <Russell.Keane@inps.co.uk>
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