From: Chris Hoover <revoohc@gmail.com>
To: smiley2211 <smiley2211@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query is taking 5 HOURS to Complete on 8.1 version
Date: Tue, 3 Jul 2007 16:16:50 -0400
Message-ID: <1d219a6f0707031316h14cd7b8cw2a5083ed4396d640@mail.gmail.com> (raw)
In-Reply-To: <11418991.post@talk.nabble.com>
References: <11416966.post@talk.nabble.com>
<11418991.post@talk.nabble.com>
On 7/3/07, smiley2211 <smiley2211@yahoo.com> wrote:
> 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;
Get rid of the order by on this view. It is a waste of resources. If you
need it ordered else where, order it on the fly i.e. select * from
temp_consent2 order by .....
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));
Try linking the people and temp_consent2 like this
where people.id not in (select temp_consent2.id from temp_consent2 where
temp_consent2.id = people.id)
That will help a lot.
HTH,
Chris
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: revoohc@gmail.com, smiley2211@yahoo.com
Subject: Re: Query is taking 5 HOURS to Complete on 8.1 version
In-Reply-To: <1d219a6f0707031316h14cd7b8cw2a5083ed4396d640@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox