Poor VIEW performance

[smiley2211]

Hello all,

I am NEW to Postgresql and I have a VIEW that is performing HORRIBLY...I have vacuumed the database and rebuild indexes...when I try to run EXPLAIN ANALYZE the results never come back...I did run an EXPLAIN and received results...this view I am told use to take 1 minute now it's taking 45 mins MINIMUM...

Please help - where should I be looking??

Thanks...

 

[feherke]

Hi

Please help - where should I be looking??

In the statement. There we would look also, but we can not...

If you want help on this, you will have to post the [tt]view[/tt] and maybe some table structures too.

Feherke.

http://rootshell.be/~feherke/

 

[smiley2211]

My query is SIMPLE SELECT: select 1 from people_consent LIMIT 1;

VIEWS:

CREATE OR REPLACE VIEW temp_consent AS
SELECT p.id, max(en.enrolled_at) AS daterecorded, a.answer
FROM people p, enrollments en, encounters ec, encounters_questions_answers eqa, questions_answers qa, answers a
WHERE (qa.question_tag::text = 'consentTransfer'::text OR qa.question_tag::text = 'shareWithEval'::text) AND eqa.question_answer_id = qa.id AND ec.id = eqa.encounter_id AND ec.enrollment_id = en.id AND p.id = en.person_id AND qa.answer_id = a.id
GROUP BY p.id, a.answer
UNION
SELECT p.id, max(c.entered_at) AS daterecorded, a.answer
FROM people p, ctccalls c, ctccalls_questions_answers cqa, questions_answers qa, answers a
WHERE (qa.question_tag::text = 'consentTransfer'::text OR qa.question_tag::text = 'shareWithEval'::text) AND cqa.question_answer_id = qa.id AND c.id = cqa.call_id AND p.id = c.person_id AND qa.answer_id = a.id
GROUP BY p.id, a.answer;


CREATE OR REPLACE VIEW temp_consent2 AS
SELECT DISTINCT temp_consent.id, temp_consent.daterecorded
FROM temp_consent
WHERE temp_consent.answer::text = 'Yes'::text
ORDER BY temp_consent.daterecorded DESC, temp_consent.id;

CREATE OR REPLACE VIEW people_consent AS
SELECT people.id, people.firstname, people.lastname, people.homephone, people.workphone, people.altphone, people.eligibilityzipcode, people.address1, people.address2, people.city, people.state, people.zipcode1, people.zipcode2, people.email, people.dayofbirth, people.monthofbirth, people.yearofbirth, people.ethnic_detail, people.external_id, people.highestlevelofeducation_id, people.ethnicgroup_id, people.ethnicotherrace, people.entered_at, people.entered_by, people.besttimetoreach_id, people.language_id, people.otherlanguage, people.gender_id, people.hispaniclatino_id, people.canscheduleapt_id, people.mayweleaveamessage_id, people.ethnictribe, people.ethnicasian, people.ethnicislander
FROM people
WHERE (people.id IN ( SELECT temp_consent2.id
FROM temp_consent2))
UNION
SELECT people.id, '***MASKED***' AS firstname, '***MASKED***' AS lastname, '***MASKED***' AS homephone, '***MASKED***' AS workphone, '***MASKED***' AS altphone, '***MASKED***' AS eligibilityzipcode, '***MASKED***' AS address1, '***MASKED***' AS address2, '***MASKED***' AS city, '***MASKED***' AS state, '***MASKED***' AS zipcode1, '***MASKED***' AS zipcode2, people.email, '***MASKED***' AS dayofbirth, '***MASKED***' AS monthofbirth, '***MASKED***' AS yearofbirth, people.ethnic_detail, people.external_id, people.highestlevelofeducation_id, people.ethnicgroup_id, people.ethnicotherrace, people.entered_at, people.entered_by, people.besttimetoreach_id, people.language_id, people.otherlanguage, people.gender_id, people.hispaniclatino_id, people.canscheduleapt_id, people.mayweleaveamessage_id, people.ethnictribe, people.ethnicasian, people.ethnicislander
FROM people
WHERE NOT (people.id IN ( SELECT temp_consent2.id
FROM temp_consent2));

Explain analyze output:

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
Limit (cost=300030114.25..300030114.34 rows=1 width=8) (actual time=20448305.393..20448305.395 rows=1 loops=1)
-> Subquery Scan people_consent (cost=300030114.25..300030427.42 rows=3212 width=8) (actual time=20448305.389..20448305.389 rows=1 loops=1)
-> Unique (cost=300030114.25..300030395.30 rows=3212 width=818) (actual time=20448305.385..20448305.385 rows=1 loops=1)
-> Sort (cost=300030114.25..300030122.28 rows=3212 width=818) (actual time=20448305.381..20448305.381 rows=1 loops=1)
Sort Key: id, firstname, lastname, homephone, workphone, altphone, eligibilityzipcode, address1, address2, city, state, zipcode1, zipcode2, email, dayofbirth, monthofbirth, yearofbirth, ethnic_detail, externa
l_id, highestlevelofeducation_id, ethnicgroup_id, ethnicotherrace, entered_at, entered_by, besttimetoreach_id, language_id, otherlanguage, gender_id, hispaniclatino_id, canscheduleapt_id, mayweleaveamessage_id, ethnictribe, ethni
casian, ethnicislander
-> Append (cost=100014441.56..300029198.26 rows=3212 width=818) (actual time=10204174.045..20448267.874 rows=6421 loops=1)
-> Nested Loop (cost=100014441.56..100014452.32 rows=2 width=818) (actual time=10204174.042..10204259.203 rows=5526 loops=1)
-> Unique (cost=100014441.56..100014441.57 rows=2 width=8) (actual time=10204173.978..10204200.737 rows=5526 loops=1)
-> Sort (cost=100014441.56..100014441.56 rows=2 width=8) (actual time=10204173.974..10204184.251 rows=7452 loops=1)
Sort Key: temp_consent2.id
-> Unique (cost=100014441.51..100014441.53 rows=2 width=16) (actual time=10204123.101..10204152.987 rows=7452 loops=1)
-> Sort (cost=100014441.51..100014441.52 rows=2 width=16) (actual time=10204123.098..10204133.841 rows=7452 loops=1)
Sort Key: temp_consent.daterecorded, temp_consent.id
-> Subquery Scan temp_consent (cost=100014441.46..100014441.50 rows=2 width=16) (actual time=10204052.222..10204100.255 rows=7452 loops=1)
-> Unique (cost=100014441.46..100014441.48 rows=2 width=36) (actual time=10204052.216..10204081.819 rows=7452 loops=1)
-> Sort (cost=100014441.46..100014441.47 rows=2 width=36) (actual time=10204052.213..10204062.389 rows=7452 loops=1)
Sort Key: id, daterecorded, answer
-> Append (cost=100014353.97..100014441.45 rows=2 width=36) (actual time=10203750.345..10204029.758 rows=7452 loops=1)
-> HashAggregate (cost=100014353.97..100014353.98 rows=1 width=36) (actual time=10203750.341..10203757.132 rows=3696 loops=1)
-> Nested Loop (cost=100000065.83..100014353.96 rows=1 width=36) (actual time=362.334..10203714.542 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014348.59 rows=1 width=36) (actual time=362.276..10203491.153 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014344.48 rows=1 width=24) (actual time=362.216..10203265.131 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014341.45 rows=1 width=24) (actual time=362.130..10202945.271 rows=3732 loops=1)
Join Filter: ("inner".question_answer_id = "outer".id)
-> Nested Loop (cost=65.83..75.86 rows=1 width=28) (actual time=15.432..176.908 rows=7596 loops=1)
-> Index Scan using answers_answer_un on answers a (cost=0.00..6.01 rows=1 width=28) (actual time=0.079..0.084
rows=1 loops=1)
Index Cond: ((answer)::text = 'Yes'::text)
-> Bitmap Heap Scan on questions_answers qa (cost=65.83..69.84 rows=1 width=16) (actual time=15.342..145.161 r
ows=7596 loops=1)
Recheck Cond: ((qa.answer_id = "outer".id) AND (((qa.question_tag)::text = 'consentTransfer'::text) OR ((q
a.question_tag)::text = 'shareWithEval'::text)))
-> BitmapAnd (cost=65.83..65.83 rows=1 width=0) (actual time=14.614..14.614 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_answer_id (cost=0.00..2.40 rows=115 width=0) (actual time=10.820..10
.820 rows=60646 loops=1)
Index Cond: (qa.answer_id = "outer".id)
-> BitmapOr (cost=63.17..63.17 rows=7764 width=0) (actual time=2.731..2.731 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..49.94 rows=6270 width=0) (actual time=2.067..
2.067 rows=6429 loops=1)
Index Cond: ((question_tag)::text = 'consentTransfer'::text)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..13.23 rows=1494 width=0) (actual time=0.658..
0.658 rows=2049 loops=1)
Index Cond: ((question_tag)::text = 'shareWithEval'::text)
-> Seq Scan on encounters_questions_answers eqa (cost=100000000.00..100008089.15 rows=494115 width=8) (actual time=0
.010..695.501 rows=494115 loops=7596)
-> Index Scan using encounters_id on encounters ec (cost=0.00..3.02 rows=1 width=8) (actual time=0.073..0.075 rows=1 loops
=3732)
Index Cond: (ec.id = "outer".encounter_id)
-> Index Scan using enrollements_pk on enrollments en (cost=0.00..4.10 rows=1 width=20) (actual time=0.051..0.053 rows=1 loops=3
732)
Index Cond: ("outer".enrollment_id = en.id)
-> Index Scan using people_pk on people p (cost=0.00..5.37 rows=1 width=8) (actual time=0.050..0.052 rows=1 loops=3732)
Index Cond: (p.id = "outer".person_id)
-> HashAggregate (cost=87.44..87.45 rows=1 width=36) (actual time=247.870..254.754 rows=3756 loops=1)
-> Nested Loop (cost=65.83..87.43 rows=1 width=36) (actual time=15.694..237.786 rows=3858 loops=1)
-> Nested Loop (cost=65.83..82.05 rows=1 width=36) (actual time=15.638..195.323 rows=3858 loops=1)
-> Nested Loop (cost=65.83..78.90 rows=1 width=24) (actual time=15.578..156.155 rows=3858 loops=1)
-> Nested Loop (cost=65.83..75.86 rows=1 width=28) (actual time=15.380..79.067 rows=7596 loops=1)
-> Index Scan using answers_answer_un on answers a (cost=0.00..6.01 rows=1 width=28) (actual time=0.082..0.084 rows=
1 loops=1)
Index Cond: ((answer)::text = 'Yes'::text)
-> Bitmap Heap Scan on questions_answers qa (cost=65.83..69.84 rows=1 width=16) (actual time=15.283..59.325 rows=759
6 loops=1)
Recheck Cond: ((qa.answer_id = "outer".id) AND (((qa.question_tag)::text = 'consentTransfer'::text) OR ((qa.ques
tion_tag)::text = 'shareWithEval'::text)))
-> BitmapAnd (cost=65.83..65.83 rows=1 width=0) (actual time=14.554..14.554 rows=0 loops=1)
lines 1-56

-> Bitmap Index Scan on qs_as_answer_id (cost=0.00..2.40 rows=115 width=0) (actual time=10.741..10.741 r
ows=60646 loops=1)
Index Cond: (qa.answer_id = "outer".id)
-> BitmapOr (cost=63.17..63.17 rows=7764 width=0) (actual time=2.769..2.769 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..49.94 rows=6270 width=0) (actual time=2.094..2.094
rows=6429 loops=1)
Index Cond: ((question_tag)::text = 'consentTransfer'::text)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..13.23 rows=1494 width=0) (actual time=0.668..0.668
rows=2049 loops=1)
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) (actual time=0.
005..0.006 rows=1 loops=7596)
Index Cond: (cqa.question_answer_id = "outer".id)
-> Index Scan using ctccalls_pk on ctccalls c (cost=0.00..3.14 rows=1 width=20) (actual time=0.004..0.005 rows=1 loops=3858)
Index Cond: (c.id = "outer".call_id)
-> Index Scan using people_pk on people p (cost=0.00..5.37 rows=1 width=8) (actual time=0.004..0.006 rows=1 loops=3858)
Index Cond: (p.id = "outer".person_id)
-> Index Scan using people_pk on people (cost=0.00..5.37 rows=1 width=818) (actual time=0.003..0.005 rows=1 loops=5526)
Index Cond: (people.id = "outer".id)
-> Subquery Scan "*SELECT* 2" (cost=200014441.55..200014745.91 rows=3210 width=679) (actual time=10243983.446..10243993.236 rows=895 loops=1)
-> Seq Scan on people (cost=200014441.55..200014713.81 rows=3210 width=679) (actual time=10243983.433..10243990.395 rows=895 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Subquery Scan temp_consent2 (cost=100014441.51..100014441.55 rows=2 width=8) (actual time=10243923.475..10243971.127 rows=7452 loops=1)
-> Unique (cost=100014441.51..100014441.53 rows=2 width=16) (actual time=10243923.471..10243952.453 rows=7452 loops=1)
-> Sort (cost=100014441.51..100014441.52 rows=2 width=16) (actual time=10243923.468..10243933.531 rows=7452 loops=1)
Sort Key: temp_consent.daterecorded, temp_consent.id
-> Subquery Scan temp_consent (cost=100014441.46..100014441.50 rows=2 width=16) (actual time=10243852.739..10243900.807 rows=7452 loops=1)
-> Unique (cost=100014441.46..100014441.48 rows=2 width=36) (actual time=10243852.734..10243882.357 rows=7452 loops=1)
-> Sort (cost=100014441.46..100014441.47 rows=2 width=36) (actual time=10243852.730..10243862.925 rows=7452 loops=1)
Sort Key: id, daterecorded, answer
-> Append (cost=100014353.97..100014441.45 rows=2 width=36) (actual time=10243550.620..10243830.301 rows=7452 loops=1)
-> HashAggregate (cost=100014353.97..100014353.98 rows=1 width=36) (actual time=10243550.617..10243557.384 rows=3696 loops=1)
-> Nested Loop (cost=100000065.83..100014353.96 rows=1 width=36) (actual time=359.364..10243517.592 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014348.59 rows=1 width=36) (actual time=359.349..10243295.466 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014344.48 rows=1 width=24) (actual time=359.288..10243069.677 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014341.45 rows=1 width=24) (actual time=359.201..10242751.494 rows=3732 loops=1)
Join Filter: ("inner".question_answer_id = "outer".id)
-> Nested Loop (cost=65.83..75.86 rows=1 width=28) (actual time=14.076..173.427 rows=7596 loops=1)
-> Index Scan using answers_answer_un on answers a (cost=0.00..6.01 rows=1 width=28) (actual time=0.051..0.0
55 rows=1 loops=1)
Index Cond: ((answer)::text = 'Yes'::text)
-> Bitmap Heap Scan on questions_answers qa (cost=65.83..69.84 rows=1 width=16) (actual time=14.012..142.998
rows=7596 loops=1)
Recheck Cond: ((qa.answer_id = "outer".id) AND (((qa.question_tag)::text = 'consentTransfer'::text) OR (
(qa.question_tag)::text = 'shareWithEval'::text)))
-> BitmapAnd (cost=65.83..65.83 rows=1 width=0) (actual time=13.300..13.300 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_answer_id (cost=0.00..2.40 rows=115 width=0) (actual time=9.640..9
.640 rows=60646 loops=1)
Index Cond: (qa.answer_id = "outer".id)
-> BitmapOr (cost=63.17..63.17 rows=7764 width=0) (actual time=2.612..2.612 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..49.94 rows=6270 width=0) (actual time=1.974
..1.974 rows=6429 loops=1)
Index Cond: ((question_tag)::text = 'consentTransfer'::text)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..13.23 rows=1494 width=0) (actual time=0.632
..0.632 rows=2049 loops=1)
Index Cond: ((question_tag)::text = 'shareWithEval'::text)
-> Seq Scan on encounters_questions_answers eqa (cost=100000000.00..100008089.15 rows=494115 width=8) (actual time
=0.010..700.722 rows=494115 loops=7596)
-> Index Scan using encounters_id on encounters ec (cost=0.00..3.02 rows=1 width=8) (actual time=0.074..0.075 rows=1 loo
ps=3732)
Index Cond: (ec.id = "outer".encounter_id)
-> Index Scan using enrollements_pk on enrollments en (cost=0.00..4.10 rows=1 width=20) (actual time=0.051..0.053 rows=1 loops
=3732)
Index Cond: ("outer".enrollment_id = en.id)
-> Index Scan using people_pk on people p (cost=0.00..5.37 rows=1 width=8) (actual time=0.050..0.052 rows=1 loops=3732)
Index Cond: (p.id = "outer".person_id)
-> HashAggregate (cost=87.44..87.45 rows=1 width=36) (actual time=248.158..255.036 rows=3756 loops=1)
-> Nested Loop (cost=65.83..87.43 rows=1 width=36) (actual time=15.680..238.080 rows=3858 loops=1)
-> Nested Loop (cost=65.83..82.05 rows=1 width=36) (actual time=15.623..195.467 rows=3858 loops=1)
-> Nested Loop (cost=65.83..78.90 rows=1 width=24) (actual time=15.566..155.414 rows=3858 loops=1)
-> Nested Loop (cost=65.83..75.86 rows=1 width=28) (actual time=15.442..79.082 rows=7596 loops=1)
-> Index Scan using answers_answer_un on answers a (cost=0.00..6.01 rows=1 width=28) (actual time=0.084..0.086 row
s=1 loops=1)
Sort Key: temp_consent.daterecorded, temp_consent.id
-> Subquery Scan temp_consent (cost=100014441.46..100014441.50 rows=2 width=16) (actual time=10243852.739..10243900.807 rows=7452 loops=1)
-> Unique (cost=100014441.46..100014441.48 rows=2 width=36) (actual time=10243852.734..10243882.357 rows=7452 loops=1)
-> Sort (cost=100014441.46..100014441.47 rows=2 width=36) (actual time=10243852.730..10243862.925 rows=7452 loops=1)
Sort Key: id, daterecorded, answer
-> Append (cost=100014353.97..100014441.45 rows=2 width=36) (actual time=10243550.620..10243830.301 rows=7452 loops=1)
-> HashAggregate (cost=100014353.97..100014353.98 rows=1 width=36) (actual time=10243550.617..10243557.384 rows=3696 loops=1)
-> Nested Loop (cost=100000065.83..100014353.96 rows=1 width=36) (actual time=359.364..10243517.592 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014348.59 rows=1 width=36) (actual time=359.349..10243295.466 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014344.48 rows=1 width=24) (actual time=359.288..10243069.677 rows=3732 loops=1)
-> Nested Loop (cost=100000065.83..100014341.45 rows=1 width=24) (actual time=359.201..10242751.494 rows=3732 loops=1)
Join Filter: ("inner".question_answer_id = "outer".id)
-> Nested Loop (cost=65.83..75.86 rows=1 width=28) (actual time=14.076..173.427 rows=7596 loops=1)
-> Index Scan using answers_answer_un on answers a (cost=0.00..6.01 rows=1 width=28) (actual time=0.051..0.0
55 rows=1 loops=1)
Index Cond: ((answer)::text = 'Yes'::text)
-> Bitmap Heap Scan on questions_answers qa (cost=65.83..69.84 rows=1 width=16) (actual time=14.012..142.998
rows=7596 loops=1)
Recheck Cond: ((qa.answer_id = "outer".id) AND (((qa.question_tag)::text = 'consentTransfer'::text) OR (
(qa.question_tag)::text = 'shareWithEval'::text)))
-> BitmapAnd (cost=65.83..65.83 rows=1 width=0) (actual time=13.300..13.300 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_answer_id (cost=0.00..2.40 rows=115 width=0) (actual time=9.640..9
.640 rows=60646 loops=1)
Index Cond: (qa.answer_id = "outer".id)
-> BitmapOr (cost=63.17..63.17 rows=7764 width=0) (actual time=2.612..2.612 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..49.94 rows=6270 width=0) (actual time=1.974
..1.974 rows=6429 loops=1)
Index Cond: ((question_tag)::text = 'consentTransfer'::text)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..13.23 rows=1494 width=0) (actual time=0.632
..0.632 rows=2049 loops=1)
Index Cond: ((question_tag)::text = 'shareWithEval'::text)
-> Seq Scan on encounters_questions_answers eqa (cost=100000000.00..100008089.15 rows=494115 width=8) (actual time
=0.010..700.722 rows=494115 loops=7596)
-> Index Scan using encounters_id on encounters ec (cost=0.00..3.02 rows=1 width=8) (actual time=0.074..0.075 rows=1 loo
ps=3732)
Index Cond: (ec.id = "outer".encounter_id)
-> Index Scan using enrollements_pk on enrollments en (cost=0.00..4.10 rows=1 width=20) (actual time=0.051..0.053 rows=1 loops
=3732)
Index Cond: ("outer".enrollment_id = en.id)
-> Index Scan using people_pk on people p (cost=0.00..5.37 rows=1 width=8) (actual time=0.050..0.052 rows=1 loops=3732)
Index Cond: (p.id = "outer".person_id)
-> HashAggregate (cost=87.44..87.45 rows=1 width=36) (actual time=248.158..255.036 rows=3756 loops=1)
-> Nested Loop (cost=65.83..87.43 rows=1 width=36) (actual time=15.680..238.080 rows=3858 loops=1)
-> Nested Loop (cost=65.83..82.05 rows=1 width=36) (actual time=15.623..195.467 rows=3858 loops=1)
-> Nested Loop (cost=65.83..78.90 rows=1 width=24) (actual time=15.566..155.414 rows=3858 loops=1)
-> Nested Loop (cost=65.83..75.86 rows=1 width=28) (actual time=15.442..79.082 rows=7596 loops=1)
-> Index Scan using answers_answer_un on answers a (cost=0.00..6.01 rows=1 width=28) (actual time=0.084..0.086 row
s=1 loops=1)
Index Cond: ((answer)::text = 'Yes'::text)
-> Bitmap Heap Scan on questions_answers qa (cost=65.83..69.84 rows=1 width=16) (actual time=15.343..59.389 rows=7
596 loops=1)
Recheck Cond: ((qa.answer_id = "outer".id) AND (((qa.question_tag)::text = 'consentTransfer'::text) OR ((qa.qu
estion_tag)::text = 'shareWithEval'::text)))
-> BitmapAnd (cost=65.83..65.83 rows=1 width=0) (actual time=14.620..14.620 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_answer_id (cost=0.00..2.40 rows=115 width=0) (actual time=10.778..10.778
rows=60646 loops=1)
Index Cond: (qa.answer_id = "outer".id)
-> BitmapOr (cost=63.17..63.17 rows=7764 width=0) (actual time=2.788..2.788 rows=0 loops=1)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..49.94 rows=6270 width=0) (actual time=2.096..2.09
6 rows=6429 loops=1)
Index Cond: ((question_tag)::text = 'consentTransfer'::text)
-> Bitmap Index Scan on qs_as_qtag (cost=0.00..13.23 rows=1494 width=0) (actual time=0.685..0.68
5 rows=2049 loops=1)
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) (actual time=
0.005..0.006 rows=1 loops=7596)
Index Cond: (cqa.question_answer_id = "outer".id)
-> Index Scan using ctccalls_pk on ctccalls c (cost=0.00..3.14 rows=1 width=20) (actual time=0.004..0.005 rows=1 loops=3858)
Index Cond: (c.id = "outer".call_id)
-> Index Scan using people_pk on people p (cost=0.00..5.37 rows=1 width=8) (actual time=0.004..0.006 rows=1 loops=3858)
Index Cond: (p.id = "outer".person_id)
Total runtime: 20448310.101 ms = 5.6800862 hour
(132 rows)

EXPLAIN OUTPUT:
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)

 

[ceco]

it is pretty large query, I don't have time to dive in to it, but at first glance, you can try to use UNION ALL instead of just UNION (if you are sure that there are no duplicates)

 

[feherke]

Hi

You say 45 minutes ? That is fast for those statements !

In people_consent rewrite the
[tt] FROM people
WHERE (people.id IN ( SELECT temp_consent2.id
FROM temp_consent2))[/tt]
to [tt]join[/tt]

[b]from[/b] people
[b]inner join[/b] temp_consent2 [b]using[/b] (id)

In people_consent you can change the [tt]union[/tt] to [tt]union all[/tt]. I think you will never have to deal with possible duplications. If possible do the same in temp_consent too.

I would try to eliminate the temp_consent2 [tt]view[/tt]. So I would try to [tt]join[/tt] people directly with tem_consent in the people_consent [tt]view[/tt] and moving the [tt]where[/tt] conditions there.

But all the above will make no significant difference. Personally I would change those [tt]view[/tt]s into temporary [tt]table[/tt]s. You can also take a look at Jonathan Gardner's article, "Materialized Views in PostgreSQL". I did not tried it, but looks promising.

Feherke.

http://rootshell.be/~feherke/