From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Euler Taveira de Oliveira <euler(at)timbira(dot)com> |
Cc: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: plpgsql function is so slow |
Date: | 2009-09-25 04:56:23 |
Message-ID: | 162867790909242156n1cbcf533g1d85b2650f1cb4fd@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2009-09-25 05:05:43 | Re: plpgsql function is so slow |
Previous Message | David E. Wheeler | 2009-09-25 04:52:34 | Re: latest hstore patch |