Re: Re: [COMMITTERS] pgsql: Invent a "one-shot" variant of CachedPlans for better performanc

Invent a "one-shot" variant of CachedPlans for better performance.

SPI_execute() and related functions create a CachedPlan, execute it once,
and immediately discard it, so that the functionality offered by
plancache.c is of no value in this code path. And performance measurements
show that the extra data copying and invalidation checking done by
plancache.c slows down simple queries by 10% or more compared to 9.1.
However, enough of the SPI code is shared with functions that do need plan
caching that it seems impractical to bypass plancache.c altogether.
Instead, let's invent a variant version of cached plans that preserves
99% of the API but doesn't offer any of the actual functionality, nor the
overhead. This puts SPI_execute() performance back on par, or maybe even
slightly better, than it was before. This change should resolve recent
complaints of performance degradation from Dong Ye, Pavel Stehule, and
others.

By avoiding data copying, this change also reduces the amount of memory
needed to execute many-statement SPI_execute() strings, as for instance in
a recent complaint from Tomas Vondra.

An additional benefit of this change is that multi-statement SPI_execute()
query strings are now processed fully serially, that is we complete
execution of earlier statements before running parse analysis and planning
on following ones. This eliminates a long-standing POLA violation, in that
DDL that affects the behavior of a later statement will now behave as
expected.

Back-patch to 9.2, since this was a performance regression compared to 9.1.
(In 9.2, place the added struct fields so as to avoid changing the offsets
of existing fields.)

Heikki Linnakangas and Tom Lane

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/94afbd5831fbc1926f1c367ac14a45ccc29d313d

Modified Files
--------------
doc/src/sgml/spi.sgml | 25 +++--
src/backend/executor/spi.c | 144 +++++++++++++++++++++++---
src/backend/utils/cache/plancache.c | 191 +++++++++++++++++++++++++++++------
src/include/executor/spi_priv.h | 7 ++
src/include/utils/plancache.h | 19 +++-
5 files changed, 329 insertions(+), 57 deletions(-)

On 4 January 2013 22:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Invent a "one-shot" variant of CachedPlans for better performance.
...
> Back-patch to 9.2, since this was a performance regression compared to 9.1.
> (In 9.2, place the added struct fields so as to avoid changing the offsets
> of existing fields.)
>
> Heikki Linnakangas and Tom Lane

Just a moment of reflection here but I did already invent a "one-shot"
plan concept in a patch submission to 9.2, called exactly that, which
enables a variety of optimizations, this being one.

It's a shame that my patch was deferred in favour of your approach,
yet it never happened until now, well after release. Given that I
raised this before the first CF of 9.2, that is not good.

We need a full "one-shot" concept linking the planner and executor for
all sorts of reasons, not just this one. We can discuss the
practicality of individual optimizations but the linkage should be
clear throughout the whole infrastructure.

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

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On 4 January 2013 22:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Invent a "one-shot" variant of CachedPlans for better performance.

> Just a moment of reflection here but I did already invent a "one-shot"
> plan concept in a patch submission to 9.2, called exactly that, which
> enables a variety of optimizations, this being one.

If you're speaking of
http://archives.postgresql.org/pgsql-hackers/2011-06/msg01168.php

that patch was vastly more invasive than this one, with vastly less
clear performance characteristics (none of which were documented by test
cases). And it had the potential for unexpected user-visible semantics
changes; for instance, as submitted it made EXPLAIN produce results
different from what might actually happen at execution. I don't think
there's any comparison to this patch at all except in the nomenclature.

> We need a full "one-shot" concept linking the planner and executor for
> all sorts of reasons, not just this one. We can discuss the
> practicality of individual optimizations but the linkage should be
> clear throughout the whole infrastructure.

I thought then, and I think now, that such a concept was too squishy
to be useful as an actual guide to what to change. The particular
arguments you advanced then have been overtaken by events anyway;
notably that Marti Raudsepp's work on caching stable subexpressions at
execution seems like a much more general answer to the problem of
handling stable functions efficiently.

regards, tom lane

On 5 January 2013 19:15, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>> We need a full "one-shot" concept linking the planner and executor for
>> all sorts of reasons, not just this one. We can discuss the
>> practicality of individual optimizations but the linkage should be
>> clear throughout the whole infrastructure.
>
> I thought then, and I think now, that such a concept was too squishy
> to be useful as an actual guide to what to change. The particular
> arguments you advanced then have been overtaken by events anyway;
> notably that Marti Raudsepp's work on caching stable subexpressions at
> execution seems like a much more general answer to the problem of
> handling stable functions efficiently.

I knew, and accepted that that specific optimization has been
superceded. My point is that the "one-shot" situation lends itself to
a great many optimizations and if we can pass that concept through, we
can simply get on with implementing them.

Having the planner pretend that it doesn't know what will happen at
execution time isn't sensible. Having the executor know its a one-off
will surely help somewhere along the line. It's hardly a modularity
violation to pass small, yet major pieces of information across the
divide.

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