PL/pgSQL and SPI

Hi,

1. I've just committed some changes to PL/pgSQL and the SPI
manager.

It's a speedup of PL/pgSQL execution by calling
ExecEvalExpr() in the executor directly for simple
expressions that return one single Datum.

For the speed test I've removed all the setup stuff from
the plpgsql regression and ran the normal queries all in
one transaction. There are 196 query plans generated
during the regression and only 37 are left now for which
PL/pgSQL really calls SPI_execp().

This saves 30% of total execution time! I don't know how
much of the whole execution time is spent in PL/pgSQL and
how much is consumed by the normal query processing.

In another test I used a silly add function that simply
does a "return $1 + $2" and built a sum() aggregate on
top of it. In that case 65% of execution time to
summarize 20000 int4 values where saved. This is a
speedup by factor 3.

To be able to do so I've moved some of the declarations
from spi.c into a new header spi_priv.h so someone has
access to the _SPI_plan structure for past preparing
plan-/querytree analysis. And I've added two silly
functions SPI_push() and SPI_pop() that simply
increment/decrement the _SPI_curid value. This is
required for calling ExecEvalExpr(), because there could
be functions evaluated that use SPI themself and
otherwise they could not connect to the SPI manager. They
are dangerous and I'm in doubt if we should document
them.

2. While doing the above I've encountered some bad details
of the SPI manager and the executor. The Func and Oper
nodes point to a function cache, which is initially NULL
and is not copied by copyNode().

For every call of SPI_execp() to execute a prepared plan,
the whole plan is copied into the current memory context.
Since this clears out the fcache, the executor has to do
several syscache lookups for every function or operator
hit during execution of the plan.

Unfortunately I haven't found a way yet to avoid it.
Anything I tried so far ended in coredumps or other
misbehaviour. Maybe someone else has an idea.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #