Re: Transient plans versus the SPI API

On Tue, 2011-08-02 at 16:47 -0400, Tom Lane 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.

Would there ultimately be a difference between the way SPI_prepare and
PQprepare work? It seems like the needs would be about the same, so I
think we should be consistent.

Also, I assume that SPI_execute and PQexecParams would always force a
custom plan, just like always, right?

A control knob sounds limited. For instance, what if the application
knows that some parameters will be constant over the time that the plan
is saved? It would be nice to be able to bind some parameters to come up
with a generic (but less generic) plan, and then execute it many times.
Right now that can only be done by inlining such constants in the SQL,
which is what we want to avoid.

I'm a little bothered by "prepare" sometimes planning and sometimes not
(and, by implication, "execute_plan" sometimes planning and sometimes
not). It seems cleaner to just separate the steps into parse+rewrite,
bind parameters, plan (with whatever parameters are present, giving a
more generic plan when some aren't specified), and execute (which would
require you to specify any parameters not bound yet). Maybe we don't
need to expose all of those steps (although maybe we do), but it would
be nice if the API we do offer resembles those steps.

Regards,
Jeff Davis