From: Jim C. Nasby <jnasby@pervasive.com>
To: Shaun Thomas <sthomas@leapfrogonline.com>
Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Confirmation of bad query plan generated by 7.4
Date: Tue, 13 Jun 2006 17:54:23 -0500
Message-ID: <20060613225423.GN34196@pervasive.com> (raw)
In-Reply-To: <448EF8B2.8F27.00A9.0@leapfrogonline.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>
On Tue, Jun 13, 2006 at 05:41:06PM -0500, Shaun Thomas wrote:
> >>> On 6/13/2006 at 4:54 PM, "Jim C. Nasby" <jnasby@pervasive.com>
> 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
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: jnasby@pervasive.com, sthomas@leapfrogonline.com, tgl@sss.pgh.pa.us
Subject: Re: Confirmation of bad query plan generated by 7.4
In-Reply-To: <20060613225423.GN34196@pervasive.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