Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 0D57B9FA4AA for ; Tue, 3 Jul 2007 17:16:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 21507-02 for ; Tue, 3 Jul 2007 17:16:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 98E6A9FB1EF for ; Tue, 3 Jul 2007 17:16:52 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so188390ugf for ; Tue, 03 Jul 2007 13:16:51 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Isi1l2BdKIZ8HnGlsv1pl7N6ljkmV7D/pyEs/6LicDyy5Gq/F5dM6N/eSa47p7XuvOWNnX3CyGq4+WIgDo2hWWBjq9oH7NM0Ahe0aFVOemG7KE0zDCUYm525Wz8SjGhOdAy37q9ikjT+SO4uzG7AbsGtdqUcNPcAhOZNYu5a7k0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Vbx7uVkFKXV01/zgVLiMaAHSkpPOMFAKbdaslQiVcjn/nMgSQWaDItPM6Jc24M7qx76Dhgjf0XF6QVeumO0N9NFI2S6CFBCTKcrlqIOdBzls3JKLHtKGU+Z+WbLGcoJBt5QpUbOTVQs3IrrvlbulLj3FyX1GUBqu6dc4s3Wgbjs= Received: by 10.78.171.13 with SMTP id t13mr3775944hue.1183493810401; Tue, 03 Jul 2007 13:16:50 -0700 (PDT) Received: by 10.78.201.16 with HTTP; Tue, 3 Jul 2007 13:16:50 -0700 (PDT) Message-ID: <1d219a6f0707031316h14cd7b8cw2a5083ed4396d640@mail.gmail.com> Date: Tue, 3 Jul 2007 16:16:50 -0400 From: "Chris Hoover" To: smiley2211 Subject: Re: Query is taking 5 HOURS to Complete on 8.1 version Cc: pgsql-performance@postgresql.org In-Reply-To: <11418991.post@talk.nabble.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_102877_8243255.1183493810342" References: <11416966.post@talk.nabble.com> <11418991.post@talk.nabble.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/22 X-Sequence-Number: 25706 ------=_Part_102877_8243255.1183493810342 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/3/07, smiley2211 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 ------=_Part_102877_8243255.1183493810342 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_102877_8243255.1183493810342--