Re: Confirmation of bad query plan generated by 7.4

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(at)pervasive(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461