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.
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