Re: Query is taking 5 HOURS to Complete on 8.1 version - Mailing list pgsql-performance
| From | smiley2211 |
|---|---|
| Subject | Re: Query is taking 5 HOURS to Complete on 8.1 version |
| Date | |
| Msg-id | 11418557.post@talk.nabble.com Whole thread Raw |
| In response to | Query is taking 5 HOURS to Complete on 8.1 version (smiley2211 <smiley2211@yahoo.com>) |
| Responses |
Re: Query is taking 5 HOURS to Complete on 8.1 version
(Richard Huxton <dev@archonet.com>)
|
| List | pgsql-performance |
Here is the EXPLAIN after I changed some conf file - now I am running another
EXPLAIN ANALYZE which may take 5 or more hours to complete :,(
effective_cache = 170000
enable_seqscan = on
enable _bitmapscan = on
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-
Limit (cost=27674.12..27674.21 rows=1 width=8)
-> Subquery Scan people_consent (cost=27674.12..27978.41 rows=3121
width=8)
-> Unique (cost=27674.12..27947.20 rows=3121 width=816)
-> Sort (cost=27674.12..27681.92 rows=3121 width=816)
Sort Key: id, firstname, lastname, homephone,
workphone, al
tphone, eligibilityzipcode, address1, address2, city, state, zipcode1,
zipcode2,
email, dayofbirth, monthofbirth, yearofbirth, ethnic_detail, external_id,
highe
stlevelofeducation_id, ethnicgroup_id, ethnicotherrace, entered_at,
entered_by,
besttimetoreach_id, language_id, otherlanguage, gender_id,
hispaniclatino_id, ca
nscheduleapt_id, mayweleaveamessage_id, ethnictribe, ethnicasian,
ethnicislander
-> Append (cost=13595.19..27492.98 rows=3121
width=816)
-> Nested Loop (cost=13595.19..13602.61 rows=2
widt
h=816)
-> Unique (cost=13595.19..13595.20 rows=2
wid
th=8)
-> Sort (cost=13595.19..13595.19
rows=2
width=8)
Sort Key: temp_consent2.id
-> Unique
(cost=13595.14..13595.1
6 rows=2 width=16)
-> Sort
(cost=13595.14..135
95.15 rows=2 width=16)
Sort Key:
temp_consent.
daterecorded, temp_consent.id
-> Subquery Scan
temp_
consent (cost=13595.09..13595.13 rows=2 width=16)
-> Unique
(cost
=13595.09..13595.11 rows=2 width=36)
->
Sort (
cost=13595.09..13595.10 rows=2 width=36)
Sort
Key: id, daterecorded, answer
-> A
ppend (cost=13506.81..13595.08 rows=2 width=36)
-> HashAggregate (cost=13506.81..13506.83 rows=1 width=36)
-> Nested Loop (cost=58.47..13506.81 rows=1 width=36)
-> Nested Loop (cost=58.47..13503.10 rows=1 width=36)
-> Nested Loop (cost=58.47..13499.67 rows=1 width=24)
-> Nested Loop (cost=58.47..13496.64 rows=1
width=24)
Join Filter: ("inner".question_answer_id =
"outer
".id)
-> Nested Loop (cost=58.47..78.41 rows=1
width=
28)
-> Index Scan using answers_answer_un
on a
nswers a (cost=0.00..4.01 rows=1 width=28)
Index Cond: ((answer)::text =
'Yes'::
text)
-> Bitmap Heap Scan on
questions_answers q
a (cost=58.47..74.30 rows=8 width=16)
Recheck Cond: ((qa.answer_id =
"outer
".id) AND (((qa.question_tag)::text = 'consentTransfer'::text) OR
((qa.question_
tag)::text = 'shareWithEval'::text)))
-> BitmapAnd (cost=58.47..58.47
row
s=8 width=0)
-> Bitmap Index Scan on
qs_as_
answer_id (cost=0.00..5.37 rows=677 width=0)
Index Cond:
(qa.answer_id
= "outer".id)
-> BitmapOr
(cost=52.85..52.8
5 rows=6530 width=0)
-> Bitmap Index Scan
on
qs_as_qtag (cost=0.00..26.43 rows=3265 width=0)
Index Cond:
((quest
ion_tag)::text = 'consentTransfer'::text)
-> Bitmap Index Scan
on
qs_as_qtag (cost=0.00..26.43 rows=3265 width=0)
Index Cond:
((quest
ion_tag)::text = 'shareWithEval'::text)
-> Seq Scan on encounters_questions_answers
eqa
(cost=0.00..7608.66 rows=464766 width=8)
-> Index Scan using encounters_id on encounters ec
(c
ost=0.00..3.01 rows=1 width=8)
Index Cond: (ec.id = "outer".encounter_id)
-> Index Scan using enrollements_pk on enrollments en
(cost
=0.00..3.42 rows=1 width=20)
Index Cond: ("outer".enrollment_id = en.id)
-> Index Scan using people_pk on people p (cost=0.00..3.69
rows=1
width=8)
Index Cond: (p.id = "outer".person_id)
-> HashAggregate (cost=88.22..88.24 rows=1 width=36)
-> Nested Loop (cost=58.47..88.22 rows=1 width=36)
-> Nested Loop (cost=58.47..84.51 rows=1 width=36)
-> Nested Loop (cost=58.47..81.43 rows=1 width=24)
-> Nested Loop (cost=58.47..78.41 rows=1
width=28)
-> Index Scan using answers_answer_un on
answers
a (cost=0.00..4.01 rows=1 width=28)
Index Cond: ((answer)::text =
'Yes'::text)
-> Bitmap Heap Scan on questions_answers qa
(co
st=58.47..74.30 rows=8 width=16)
Recheck Cond: ((qa.answer_id =
"outer".id)
AND (((qa.question_tag)::text = 'consentTransfer'::text) OR
((qa.question_tag)::
text = 'shareWithEval'::text)))
-> BitmapAnd (cost=58.47..58.47
rows=8 wi
dth=0)
-> Bitmap Index Scan on
qs_as_answer
_id (cost=0.00..5.37 rows=677 width=0)
Index Cond: (qa.answer_id =
"ou
ter".id)
-> BitmapOr (cost=52.85..52.85
rows
=6530 width=0)
-> Bitmap Index Scan on
qs_as_
qtag (cost=0.00..26.43 rows=3265 width=0)
Index Cond:
((question_ta
g)::text = 'consentTransfer'::text)
-> Bitmap Index Scan on
qs_as_
qtag (cost=0.00..26.43 rows=3265 width=0)
Index Cond:
((question_ta
g)::text = 'shareWithEval'::text)
-> Index Scan using ctccalls_qs_as_qaid on
ctccalls_qu
estions_answers cqa (cost=0.00..3.02 rows=1 width=8)
Index Cond: (cqa.question_answer_id =
"outer".id)
-> Index Scan using ctccalls_pk on ctccalls c
(cost=0.00..3
.06 rows=1 width=20)
Index Cond: (c.id = "outer".call_id)
-> Index Scan using people_pk on people p (cost=0.00..3.69
rows=1
width=8)
Index Cond: (p.id = "outer".person_id)
-> Index Scan using people_pk on people
(cost
=0.00..3.69 rows=1 width=816)
Index Cond: (people.id = "outer".id)
-> Subquery Scan "*SELECT* 2"
(cost=13595.18..13890
.35 rows=3119 width=677)
-> Seq Scan on people
(cost=13595.18..13859.1
6 rows=3119 width=677)
Filter: (NOT (hashed subplan))
SubPlan
-> Subquery Scan temp_consent2
(cost=
13595.14..13595.18 rows=2 width=8)
-> Unique
(cost=13595.14..13595
.16 rows=2 width=16)
-> Sort
(cost=13595.14..1
3595.15 rows=2 width=16)
Sort Key:
temp_consen
t.daterecorded, temp_consent.id
-> Subquery Scan
tem
p_consent (cost=13595.09..13595.13 rows=2 width=16)
-> Unique
(co
st=13595.09..13595.11 rows=2 width=36)
->
Sort
(cost=13595.09..13595.10 rows=2 width=36)
Sor
t Key: id, daterecorded, answer
->
Append (cost=13506.81..13595.08 rows=2 width=36)
-> HashAggregate (cost=13506.81..13506.83 rows=1 width=36)
-> Nested Loop (cost=58.47..13506.81 rows=1 width=36)
-> Nested Loop (cost=58.47..13503.10 rows=1 width=36)
-> Nested Loop (cost=58.47..13499.67 rows=1 width=24)
-> Nested Loop (cost=58.47..13496.64 rows=1
width=2
4)
Join Filter: ("inner".question_answer_id =
"out
er".id)
-> Nested Loop (cost=58.47..78.41 rows=1
widt
h=28)
-> Index Scan using
answers_answer_un on
answers a (cost=0.00..4.01 rows=1 width=28)
Index Cond: ((answer)::text =
'Yes'
::text)
-> Bitmap Heap Scan on
questions_answers
qa (cost=58.47..74.30 rows=8 width=16)
Recheck Cond: ((qa.answer_id =
"out
er".id) AND (((qa.question_tag)::text = 'consentTransfer'::text) OR
((qa.questio
n_tag)::text = 'shareWithEval'::text)))
-> BitmapAnd
(cost=58.47..58.47 r
ows=8 width=0)
-> Bitmap Index Scan on
qs_a
s_answer_id (cost=0.00..5.37 rows=677 width=0)
Index Cond:
(qa.answer_
id = "outer".id)
-> BitmapOr
(cost=52.85..52
.85 rows=6530 width=0)
-> Bitmap Index
Scan o
n qs_as_qtag (cost=0.00..26.43 rows=3265 width=0)
Index Cond:
((que
stion_tag)::text = 'consentTransfer'::text)
-> Bitmap Index
Scan o
n qs_as_qtag (cost=0.00..26.43 rows=3265 width=0)
Index Cond:
((que
stion_tag)::text = 'shareWithEval'::text)
-> Seq Scan on
encounters_questions_answers eq
a (cost=0.00..7608.66 rows=464766 width=8)
-> Index Scan using encounters_id on encounters
ec
(cost=0.00..3.01 rows=1 width=8)
Index Cond: (ec.id = "outer".encounter_id)
-> Index Scan using enrollements_pk on enrollments en
(co
st=0.00..3.42 rows=1 width=20)
Index Cond: ("outer".enrollment_id = en.id)
-> Index Scan using people_pk on people p (cost=0.00..3.69
rows
=1 width=8)
Index Cond: (p.id = "outer".person_id)
-> HashAggregate (cost=88.22..88.24 rows=1 width=36)
-> Nested Loop (cost=58.47..88.22 rows=1 width=36)
-> Nested Loop (cost=58.47..84.51 rows=1 width=36)
-> Nested Loop (cost=58.47..81.43 rows=1 width=24)
-> Nested Loop (cost=58.47..78.41 rows=1
width=28)
-> Index Scan using answers_answer_un on
answe
rs a (cost=0.00..4.01 rows=1 width=28)
Index Cond: ((answer)::text =
'Yes'::text
)
-> Bitmap Heap Scan on questions_answers
qa (
cost=58.47..74.30 rows=8 width=16)
Recheck Cond: ((qa.answer_id =
"outer".id
) AND (((qa.question_tag)::text = 'consentTransfer'::text) OR
((qa.question_tag)
::text = 'shareWithEval'::text)))
-> BitmapAnd (cost=58.47..58.47
rows=8
width=0)
-> Bitmap Index Scan on
qs_as_answ
er_id (cost=0.00..5.37 rows=677 width=0)
Index Cond: (qa.answer_id
= "
outer".id)
-> BitmapOr
(cost=52.85..52.85 ro
ws=6530 width=0)
-> Bitmap Index Scan on
qs_a
s_qtag (cost=0.00..26.43 rows=3265 width=0)
Index Cond:
((question_
tag)::text = 'consentTransfer'::text)
-> Bitmap Index Scan on
qs_a
-> Bitmap Index Scan on
qs_a
s_qtag (cost=0.00..26.43 rows=3265 width=0)
Index Cond:
((question_
tag)::text = 'shareWithEval'::text)
-> Index Scan using ctccalls_qs_as_qaid on
ctccalls_
questions_answers cqa (cost=0.00..3.02 rows=1 width=8)
Index Cond: (cqa.question_answer_id =
"outer".i
d)
-> Index Scan using ctccalls_pk on ctccalls c
(cost=0.00.
.3.06 rows=1 width=20)
Index Cond: (c.id = "outer".call_id)
-> Index Scan using people_pk on people p (cost=0.00..3.69
rows
=1 width=8)
Index Cond: (p.id = "outer".person_id)
(131 rows)
--
View this message in context:
http://www.nabble.com/Query-is-taking-5-HOURS-to-Complete-on-8.1-version-tf4019778.html#a11418557
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
pgsql-performance by date: