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/
