Re: Avoiding bad prepared-statement plans. - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Avoiding bad prepared-statement plans.
Date
Msg-id 201002110315.o1B3F1R12683@momjian.us
Whole thread Raw
In response to Re: Avoiding bad prepared-statement plans.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen <jtv@xs4all.nl> wrote:
> >> Periodically re-plan prepared statements on EXECUTE. �This is also a chance
> >> for queries that were being re-planned every time to go back to a generic
> >> plan.
> 
> > The most common problem here seems to be that (some?) MCVs need
> > different treatment than non-MCVs, so I don't think periodically
> > replanning is going to help very much.
> 
> It won't help at all.  The only reason for replanning is if something
> about the schema or the statistics change, and we already have got
> automatic cached-plan invalidation in both those cases.  If you replan
> simply because some time has elapsed, you'll just get exactly the
> same plan.
> 
> The only case that I think still has any merit is where you get a
> significantly better plan with known parameter values than without.
> The projected-cost threshold might be a reasonable approach for
> attacking that, ie, if estimated cost of generic plan exceeds X
> then take the time to build a custom plan instead.  I'm not sure that
> really will fix the problem, but it would be a very simple change to
> make to see how much it helps people.

Ideally we would do late binding (bind on the first supplied parameters,
like we do for unnamed protocol prepared queries now), and then replan
if the statistics for later parameters significantly differ from the
ones used for the the initial planning.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Avoiding bad prepared-statement plans.
Next
From: Bruce Momjian
Date:
Subject: Re: log_error_verbosity function display