PL/pgSQL and SPI - Mailing list pgsql-hackers

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@debis.com (Jan Wieck) #