Skip site navigation (1) Skip section navigation (2)

plpgsql function is so slow

From: Euler Taveira de Oliveira <euler(at)timbira(dot)com>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: plpgsql function is so slow
Date: 2009-09-24 20:51:09
Message-ID: 4ABBDBBD.4050007@timbira.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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/

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2009-09-24 21:00:52
Subject: Re: TODO item: Allow more complex user/database default GUC settings
Previous:From: Sam MasonDate: 2009-09-24 19:59:44
Subject: Re: Newbie's question: How can I connect to my postgresql-server?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group