2009/9/24 Euler Taveira de Oliveira <eu...@timbira.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers