X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 5C00D9FA38F for ; Tue, 13 Jun 2006 19:42:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 40746-03 for ; Tue, 13 Jun 2006 19:42:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.leapfrogonline.com (mail.leapfrogonline.com [69.36.35.239]) by postgresql.org (Postfix) with ESMTP id 8BEC49FA1A8 for ; Tue, 13 Jun 2006 19:42:09 -0300 (ADT) Received: from leapfrogonlinecom-MTA by mail.leapfrogonline.com with Novell_GroupWise; Tue, 13 Jun 2006 17:41:53 -0500 Message-Id: <448EF8B2.8F27.00A9.0@leapfrogonline.com> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Beta Date: Tue, 13 Jun 2006 17:41:06 -0500 From: "Shaun Thomas" To: "Jim C. Nasby" Cc: ,"Tom Lane" Subject: Re: Confirmation of bad query plan generated by 7.4 References: <9119.1150222168@sss.pgh.pa.us> <448EDFC4.8F27.00A9.0@leapfrogonline.com> <20060613211347.GB34196@pervasive.com> <448EE95D.8F27.00A9.0@leapfrogonline.com> <20060613215418.GI34196@pervasive.com> In-Reply-To: <20060613215418.GI34196@pervasive.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/221 X-Sequence-Number: 19578 >>> On 6/13/2006 at 4:54 PM, "Jim C. Nasby" wrote: > SELECT attname, attstattarget > FROM pg_attribute > WHERE attrelid='table_name'::regclass AND attnum >= 0; -1 for all values. > SHOW default_statistics_target; 10. Here's something slightly annoying: I tried precalculating the value in my stored proc, and it's still ignoring it. lastTime := now() - interval ''7 days''; UPDATE fact_credit_app SET activated_date_id = ad.date_id FROM l_event_log e JOIN c_event_type t ON (t.id = e.event_type_id) JOIN wf_date ad ON (e.event_date::date=ad.datestamp) WHERE e.ext_id=fact_credit_app.unique_id AND t.event_name = ''activation'' AND e.event_date > lastTime AND fact_credit_app.activated_date_id IS NULL; Instead of taking a handful of seconds (like when I replace lastTime with the text equivalent), it takes 10 minutes... I can see the planner not liking the results of a function, but a variable? That's a static value! ::cry:: -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwood Street Evanston, IL 60201 Tel. 847-440-8253 Fax. 847-570-5750 www.leapfrogonline.com Confidentiality Note: The document(s) accompanying this e-mail transmission, if any, and the e-mail transmittal message contain information from Leapfrog Online Customer Acquisition, LLC is confidential or privileged. The information is intended to be for the use of the individual(s) or entity(ies) named on this e-mail transmission message. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this e-mail is prohibited. If you have received this e-mail in error, please immediately delete this e-mail and notify us by telephone of the error