Re: Transient plans versus the SPI API

On Tue, Aug 2, 2011 at 4:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I've been thinking about how to redesign the plancache infrastructure to
> better support use of transient (one-shot) plans, as we've talked about
> various times such as in this thread:
> http://archives.postgresql.org/pgsql-hackers/2010-02/msg00607.php
> (Note: that thread sorta went off into the weeds arguing about exactly
> what heuristics to use for when to re-plan.  I don't want to re-open that
> issue today, since there's no way to experiment with policy until we have
> some mechanism in place.)
>
> I think that what we need to do is get rid of the assumption that a "cached
> plan" normally includes a plan per se.  The initial creation of the cache
> entry should just supply a raw query plus its analyzed-and-rewritten form.
> (plancache.c can actually operate that way today, via its "not fully
> planned" flag, but it's a wart rather than the normal philosophy.)  Then
> RevalidateCachedPlan should be replaced by something with the semantics
> of "get me a plan to use, and here's the parameter values I'm going to use
> it with".  The choice between using a pre-cached generic plan and building
> a one-off plan would then be localized in this new function.

This seems like a good design. Now what would be really cool is if
you could observe a stream of queries like this:

SELECT a, b FROM foo WHERE c = 123
SELECT a, b FROM foo WHERE c = 97
SELECT a, b FROM foo WHERE c = 236

...and say, hey, I could just make a generic plan and use it every
time I see one of these. It's not too clear to me how you'd make
recognition of such queries cheap enough to be practical, but maybe
someone will think of a way...

> There are not that many places that call plancache.c directly, and so this
> change in API won't cause much code churn --- but one place that does
> depend on this is spi.c, and there is *lots* of both core and third-party
> code that calls SPI_prepare for example.  So we need to tread carefully in
> redefining SPI's behavior.
>
> 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.
>
> Anyone have an opinion about that?  And if we do need to expose some
> control, should the default (if you don't change your source code) be that
> you still get the old behavior, or that you get the new behavior?  I'm
> inclined to think that if we believe this'll be a win at the SQL level,
> it should be a win at the SPI-caller level too, but maybe someone thinks
> otherwise.

I am not sure about this one.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company