From: Shaun Thomas <sthomas@leapfrogonline.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Confirmation of bad query plan generated by 7.4
Date: Wed, 14 Jun 2006 09:32:04 -0500
Message-ID: <448FD794.8F27.00A9.0@leapfrogonline.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 6/13/2006 at 9:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Read what you wrote, and rethink...
Hah. Yes, I understand the irony of that statement, but the point is
that the value of the variable won't change during query execution.
> If you're desperate you can construct a query string with the
variable
> value embedded as a literal, and then EXECUTE that. This isn't a
great
> solution since it forces a re-plan on every execution.
That's so gross... but it might work. I'm not really desperate, just
frustrated. I really can't wait until we can upgrade; 7.4 is driving
me nuts. I'm not really worried about a re-plan, since this SP just
updates a fact table, so it only gets called twice a day. Cutting the
execution time of the SP down to < 20 seconds from 15 minutes would be
nice, but not absolutely required. I was just surprised at the large
difference in manual execution as opposed to the SP with the same
query.
> We've occasionally debated ways to do it better, but no such
> improvement will ever appear in 7.4 ;-)
Agreed! When we finally upgrade, I fully plan on putting a symbolic
bullet into our old installation. ;)
Thanks!
--
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
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: sthomas@leapfrogonline.com, tgl@sss.pgh.pa.us, jnasby@pervasive.com
Subject: Re: Confirmation of bad query plan generated by 7.4
In-Reply-To: <448FD794.8F27.00A9.0@leapfrogonline.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