Skip site navigation (1) Skip section navigation (2)

Re: Confirmation of bad query plan generated by 7.4

From: "Shaun Thomas" <sthomas(at)leapfrogonline(dot)com>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Confirmation of bad query plan generated by 7.4
Date: 2006-06-13 22:41:06
Message-ID: 448EF8B2.8F27.00A9.0@leapfrogonline.com (view raw or flat)
Thread:
Lists: pgsql-performance
>>> 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.

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

In response to

Responses

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-06-13 22:45:23
Subject: Re: scaling up postgres
Previous:From: Jim C. NasbyDate: 2006-06-13 22:39:51
Subject: Re: Confirmation of bad query plan generated by 7.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group