smiley2211 wrote:
Here are the VIEWS in question: query = (explain analyze select id from
people_consent LIMIT 1;)
First thing I notice - you don't have any ordering, so the LIMIT isn't
returning a well-defined record. Might not matter in your particular
context.
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
I think you might be able to make this "UNION ALL" - a UNION will check
for duplicates and eliminate them. That's a match on
(id,daterecorded,answer) from both sub-queries - can that happen and do
you care?
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;
Not sure what the DISTINCT is doing for us here. You've eliminated
duplicates in the previous view and so you can't have more than one
(id,daterecorded) for any given answer. (Assuming you keep the previous
UNION in)
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));
OK, well the UNION here can certainly be UNION ALL.
1. You're using "***MASKED***" for a bunch of fields, so unless they're
occurring naturally in "people" you won't get duplicates.
2. Your WHERE clauses are the complement of each other.
One other point NOT (people.id IN...) would perhaps be usually written
as "people.id NOT IN (...)". The planner should realise they're the same
though.
However, there's one obvious thing you can do. As it stands you're
testing against temp_consent2 twice. You could rewrite the query
something like:
SELECT
people.id,
CASE WHEN temp_consent2.id IS NULL
THEN '***MASKED***'
ELSE people.firstname
END AS firstname
...
FROM
people LEFT JOIN temp_consent2 ON people.id=temp_consent2.id
;
You might want to try these tweaks, but I'd start by working with
temp_consent and seeing how long that takes to execute. Then work out.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
