Re: plpgsql function is so slow

2009/9/24 Euler Taveira de Oliveira <euler(at)timbira(dot)com>:
> Hi,
>
> I recently saw a complaint that a simple PL/PgSQL code is slower than PL/SQL.
> I did some benchmark and confirmed it is. I coded the same function
> (function2) in C just to compare with something. According to OP [1], the
> PL/SQL seems to run more than 15x faster than PL/PgSQL code.

sure - PL/SQL is translated to native code, so PL/pgSQL is only
interpret. What more - PL/SQL or C use native integer arithmetic, but
PL/pgSQL use PostgreSQL integer arithmetic.

so if you have to use fast code, use C, plperl, but not use PL/pgSQL -
it is just glue for SQL statements.

regards
Pavel Stehule

>
>
> euler=# select function1();
>  function1
> -----------
>  100000000
> (1 row)
>
> Time: 62107,607 ms
> euler=# select function2();
>  function2
> -----------
>  100000000
> (1 row)
>
> Time: 419,673 ms
>
> The PL/PgSQL function is:
>
> CREATE OR REPLACE FUNCTION function1()
> RETURNS INTEGER AS
> $BODY$
> DECLARE
>  i INTEGER;
>  s INTEGER := 0;
> BEGIN
>  FOR i IN 1 .. power(10, 8) LOOP
>     s := s + 1;
>  END LOOP;
>  RETURN s;
> END;
> $BODY$
> LANGUAGE 'plpgsql' IMMUTABLE;
>
> The C function is:
>
> #include "postgres.h"
> #include <math.h>
> #include "fmgr.h"
>
> #ifdef PG_MODULE_MAGIC
> PG_MODULE_MAGIC;
> #endif
>
> PG_FUNCTION_INFO_V1(function2);
>
> Datum function2(PG_FUNCTION_ARGS)
> {
>        int     i;
>        int s = 0;
>
>        for (i = 1; i <= (int) pow(10, 8); i++)
>                s += 1;
>
>        PG_RETURN_INT32(s);
> }
>
> PL/PgSQL oprofile is:
>
> samples  %        symbol name
> 2263     25.6024  AllocSetReset
> 1071     12.1168  ExecMakeFunctionResultNoSets
> 725       8.2023  AllocSetAlloc
> 664       7.5122  RevalidateCachedPlan
> 586       6.6297  ExecEvalParam
> 521       5.8943  AcquireExecutorLocks
> 463       5.2381  ResourceOwnerForgetPlanCacheRef
> 359       4.0615  AllocSetFreeIndex
> 329       3.7221  int4pl
> 262       2.9641  ExecEvalConst
> 248       2.8057  check_stack_depth
> 244       2.7605  MemoryContextReset
> 234       2.6474  list_head
> 143       1.6178  ReleaseCachedPlan
> 130       1.4708  MemoryContextAlloc
> 121       1.3689  pgstat_end_function_usage
> 111       1.2558  pgstat_init_function_usage
> 98        1.1087  list_head
> 94        1.0635  ResourceOwnerEnlargePlanCacheRefs
> 90        1.0182  ResourceOwnerRememberPlanCacheRef
> 44        0.4978  SPI_push
> 39        0.4412  SPI_pop
>
> Any ideas?
>
> [1]
> http://listas.postgresql.org.br/pipermail/pgbr-geral/2009-September/017427.html
>
>
> --
>  Euler Taveira de Oliveira
>  http://www.timbira.com/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>