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 AD1EA9FA38F for ; Tue, 13 Jun 2006 19:54:38 -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 40517-06 for ; Tue, 13 Jun 2006 19:54:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id B4A1D9FA1A8 for ; Tue, 13 Jun 2006 19:54:28 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id DAF4956437; Tue, 13 Jun 2006 17:54:24 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 13 Jun 2006 17:54:24 -0500 Date: Tue, 13 Jun 2006 17:54:23 -0500 From: "Jim C. Nasby" To: Shaun Thomas Cc: pgsql-performance@postgresql.org, Tom Lane Subject: Re: Confirmation of bad query plan generated by 7.4 Message-ID: <20060613225423.GN34196@pervasive.com> 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> <448EF8B2.8F27.00A9.0@leapfrogonline.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <448EF8B2.8F27.00A9.0@leapfrogonline.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060613:sthomas@leapfrogonline.com::0qwTB5jnagPeY9Hf:00000000000 00000000000000000000000009Et X-Hashcash: 1:20:060613:pgsql-performance@postgresql.org::NBiqzauZjhOJjmBl:00000 0000000000000000000000007OV5 X-Hashcash: 1:20:060613:tgl@sss.pgh.pa.us::bjjjry4cIdB9eRTM:00000000000000000000 0000000000000000000000001Iyx X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/224 X-Sequence-Number: 19581 On Tue, Jun 13, 2006 at 05:41:06PM -0500, Shaun Thomas wrote: > >>> 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. Increasing the statistics target for that table (or default_statistics_target) might help. I'd try between 50 and 100. > 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:: If you're using plpgsql, it should be turning that update into a prepared statement and then binding the variable to it. That means that if you pass in different values in the same session, you could end up with bad plans depending on the valuse, since it will cache the query plan. Actually, come to think of it... I'm not sure if bound parameters are used in query planning... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461