Re: Query is taking 5 HOURS to Complete on 8.1 version
От | smiley2211 |
---|---|
Тема | Re: Query is taking 5 HOURS to Complete on 8.1 version |
Дата | |
Msg-id | 11418991.post@talk.nabble.com обсуждение исходный текст |
Ответ на | Query is taking 5 HOURS to Complete on 8.1 version (smiley2211 <smiley2211@yahoo.com>) |
Ответы |
Re: Query is taking 5 HOURS to Complete on 8.1 version
("Chris Hoover" <revoohc@gmail.com>)
Re: Query is taking 5 HOURS to Complete on 8.1 version (Richard Huxton <dev@archonet.com>) |
Список | 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.
В списке pgsql-performance по дате отправления:
Предыдущее
От: Richard HuxtonДата:
Сообщение: Re: Query is taking 5 HOURS to Complete on 8.1 version
Следующее
От: "Chris Hoover"Дата:
Сообщение: Re: Query is taking 5 HOURS to Complete on 8.1 version