Re: plan cache overhead on plpgsql expression

[Pavel Stehule]

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

test.sql
Description: application/sql