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

Re: Avoiding bad prepared-statement plans.

From: Andres Freund <andres(at)anarazel(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-09 14:10:22
Message-ID: 201002091510.24203.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tuesday 09 February 2010 13:08:54 Jeroen Vermeulen wrote:
> I've been discussing this with Josh, Heikki, and Peter E. over the past
> few weeks.
> 
> As Peter observed years ago, prepared statements can perform badly
> because their plans are overly generic.  Also, statistics change and
> sometimes plans should change with them.  It would be nice if we could
> avoid users having to worry about these things.
> 
> I have some ideas that I'm willing to work on, if people agree that
> they're useful.  These are simple changes; the goal is to avoid
> pathological performance at minimal cost, not to make prepared
> statements faster than parameterized ones for everyone.  The ideas
> interact in various ways.
> 
> 
> = Projected-cost threshold =
> 
> If a prepared statement takes parameters, and the generic plan has a
> high projected cost, re-plan each EXECUTE individually with all its
> parameter values bound.  It may or may not help, but unless the planner
> is vastly over-pessimistic, re-planning isn't going to dominate
> execution time for these cases anyway.
> 
> = Actual-cost threshold =
> 
> Also stop using the generic plan if the statement takes a long time to
> run in practice.  Statistics may have gone bad.  It could also be a
> one-off due to a load peak or something, but that's handled by:
That is not that easy. It means that you have to use savepoints enclosing each 
and every execution of a prepared statement because the query could have 
sideeffects. Which wouldnt be terribly efficient...

Andres

In response to

Responses

pgsql-hackers by date

Next:From: Teodor SigaevDate: 2010-02-09 14:21:10
Subject: Re: [CFReview] Red-Black Tree
Previous:From: Alvaro HerreraDate: 2010-02-09 14:02:11
Subject: Re: [CFReview] Red-Black Tree

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