Re: Transient plans versus the SPI API

On Tue, Aug 2, 2011 at 9:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The most straightforward way to reimplement things within spi.c would be
> to redefine SPI_prepare as just doing the parse-and-rewrite steps, with
> planning always postponed to SPI_execute.  In the case where you just
> prepare and then execute a SPIPlan, this would come out the same or
> better, since we'd still just do one planning cycle, but the planner could
> be given the actual parameter values to use.  However, if you SPI_prepare,
> SPI_saveplan, and then SPI_execute many times, you might come out behind.
> This is of course the same tradeoff we are going to impose at the SQL level
> anyway, but I wonder whether there needs to be a control knob available to
> C code to retain the old plan-once-and-always-use-that-plan approach.

The problems only occur <1% of the time, so this penalises everyone to
avoid real but rare problems.

This will cause a massive loss of performance in most apps, though I
understand the annoyance and why you make the suggestion.

http://www.db2ude.com/?q=node/73 for some more background on how this
is handled elsewhere
Control knob == hint, so I've avoided suggesting such an approach myself.

I think its possible to tell automatically whether we need to replan
always or not based upon the path we take through selectivity
functions.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services