Re: Query is taking 5 HOURS to Complete on 8.1 version - Mailing list pgsql-performance

Here are the VIEWS in question: query = (explain analyze select id from
people_consent LIMIT 1;)

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));

--
View this message in context:
http://www.nabble.com/Query-is-taking-5-HOURS-to-Complete-on-8.1-version-tf4019778.html#a11418991
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.