Re: plan cache overhead on plpgsql expression

Hi

I did another test

I use a pi estimation algorithm and it is little bit more realistic than
just almost empty cycle body - still probably nobody will calculate pi in
plpgsql.

CREATE OR REPLACE FUNCTION pi_est(n int)
RETURNS numeric AS $$
DECLARE
accum double precision DEFAULT 1.0;
c1 double precision DEFAULT 2.0;
c2 double precision DEFAULT 1.0;
v constant double precision DEFAULT 2.0;
BEGIN
FOR i IN 1..n
LOOP
accum := accum * ((c1 * c1) / (c2 * (c2 + v)));
c1 := c1 + v;
c2 := c2 + v;
END LOOP;
RETURN accum * v;
END;
$$ LANGUAGE plpgsql;

For this code the patch increased speed for 10000000 iterations from 6.3
sec to 4.7 .. it is speedup about 25%

The best performance (28%) is with code

CREATE OR REPLACE FUNCTION pi_est_2(n int)
RETURNS numeric AS $$
DECLARE
accum double precision DEFAULT 1.0;
c1 double precision DEFAULT 2.0;
c2 double precision DEFAULT 1.0;
BEGIN
FOR i IN 1..n
LOOP
accum := accum * ((c1 * c1) / (c2 * (c2 + double precision '2.0')));
c1 := c1 + double precision '2.0';
c2 := c2 + double precision '2.0';
END LOOP;
RETURN accum * double precision '2.0';
END;
$$ LANGUAGE plpgsql;

Unfortunately for unoptimized code the performance is worse (it is about
55% slower)

CREATE OR REPLACE FUNCTION pi_est_1(n int)
RETURNS numeric AS $$
DECLARE
accum double precision DEFAULT 1.0;
c1 double precision DEFAULT 2.0;
c2 double precision DEFAULT 1.0;
BEGIN
FOR i IN 1..n
LOOP
accum := accum * ((c1 * c1) / (c2 * (c2 + 2.0)));
c1 := c1 + 2.0;
c2 := c2 + 2.0;
END LOOP;
RETURN accum * 2.0;
END;
$$ LANGUAGE plpgsql;

same performance (bad) is for explicit casting

CREATE OR REPLACE FUNCTION pi_est_3(n int)
RETURNS numeric AS $$
DECLARE
accum double precision DEFAULT 1.0;
c1 double precision DEFAULT 2.0;
c2 double precision DEFAULT 1.0;
BEGIN
FOR i IN 1..n
LOOP
accum := accum * ((c1 * c1) / (c2 * (c2 + 2.0::double precision)));
c1 := c1 + 2.0::double precision;
c2 := c2 + 2.0::double precision;
END LOOP;
RETURN accum * double precision '2.0';
END;
$$ LANGUAGE plpgsql;

There is relative high overhead of cast from numeric init_var_from_num.

On master (without patching) the speed all double precision variants is
almost same.

This example can be reduced

CREATE OR REPLACE FUNCTION public.fx(integer)
RETURNS double precision
LANGUAGE plpgsql
AS $function$
DECLARE
result double precision DEFAULT 1.0;
BEGIN
FOR i IN 1..$1
LOOP
result := result * 1.000001::double precision;
END LOOP;
RETURN result;
END;
$function$

CREATE OR REPLACE FUNCTION public.fx_1(integer)
RETURNS double precision
LANGUAGE plpgsql
AS $function$
DECLARE
result double precision DEFAULT 1.0;
BEGIN
FOR i IN 1..$1
LOOP
result := result * 1.000001;
END LOOP;
RETURN result;
END;
$function$

CREATE OR REPLACE FUNCTION public.fx_2(integer)
RETURNS double precision
LANGUAGE plpgsql
AS $function$
DECLARE
result double precision DEFAULT 1.0;
BEGIN
FOR i IN 1..$1
LOOP
result := result * double precision '1.000001';
END LOOP;
RETURN result;
END;
$function$

Patched select fx(1000000) .. 400ms, fx_1 .. 400ms, fx_2 .. 126ms
Master fx(1000000) .. 180ms, fx_1 180 ms, fx_2 .. 180ms

So the patch has a problem with constant casting - unfortunately the mix of
double precision variables and numeric constants is pretty often in
Postgres.

Regards

Pavel