Re: Inlining of couple of functions in pl_exec.c improves performance

[Pavel Stehule]

Hi

st 27. 5. 2020 v 13:31 odesílatel Amit Khandekar <amitdkhan...@gmail.com>
napsal:

> On Tue, 26 May 2020 at 09:06, Amit Khandekar <amitdkhan...@gmail.com>
> wrote:
> >
> > On Sat, 23 May 2020 at 23:24, Pavel Stehule <pavel.steh...@gmail.com>
> wrote:
> > >
> > >    FOR counter IN 1..1800000 LOOP
> > >       id = 0; id = 0; id1 = 0;
> > >       id2 = 0; id3 = 0; id1 = 0; id2 = 0;
> > >       id3 = 0; id = 0; id = 0; id1 = 0;
> > >       id2 = 0; id3 = 0; id1 = 0; id2 = 0;
> > >       id3 = 0;
> > >    END LOOP;
> > >
> > > This is not too much typical PLpgSQL code. All expressions are not
> parametrized - so this test is little bit obscure.
> > >
> > > Last strange performance plpgsql benchmark did calculation of pi
> value. It does something real
> >
> > Yeah, basically I wanted to have many statements, and that too with
> > many assignments where casts are not required. Let me check if I can
> > come up with a real-enough testcase. Thanks.
>
> create table tab (id int[]);
> insert into tab select array((select ((random() * 100000)::bigint) id
> from generate_series(1, 30000) order by 1));
> insert into tab select array((select ((random() * 600000)::bigint) id
> from generate_series(1, 30000) order by 1));
> insert into tab select array((select ((random() * 1000000)::bigint) id
> from generate_series(1, 30000) order by 1));
> insert into tab select array((select ((random() * 100000)::bigint) id
> from generate_series(1, 30000) order by 1));
> insert into tab select array((select ((random() * 600000)::bigint) id
> from generate_series(1, 30000) order by 1));
> insert into tab select array((select ((random() * 1000000)::bigint) id
> from generate_series(1, 30000) order by 1));
> insert into tab select array((select ((random() * 100000)::bigint) id
> from generate_series(1, 30000) order by 1));
> insert into tab select array((select ((random() * 600000)::bigint) id
> from generate_series(1, 30000) order by 1));
> insert into tab select array((select ((random() * 1000000)::bigint) id
> from generate_series(1, 30000) order by 1));
>
>
> -- Return how much two consecutive array elements are apart from each
> other, on average; i.e. how much the numbers are spaced out.
> -- Input is an ordered array of integers.
> CREATE OR REPLACE FUNCTION avg_space(int[]) RETURNS bigint AS $$
> DECLARE
>   diff int = 0;
>   num int;
>   prevnum int = 1;
> BEGIN
>   FOREACH num IN ARRAY $1
>   LOOP
>     diff = diff + num - prevnum;
>     prevnum = num;
>   END LOOP;
>   RETURN diff/array_length($1, 1);
> END;
> $$ LANGUAGE plpgsql;
>
> explain analyze select avg_space(id) from tab;
> Like earlier figures, these are execution times in milliseconds, taken
> from explain-analyze.
> ARM VM:
>    HEAD                             : 49.8
>    patch 0001+0002           : 47.8 => 4.2%
>    patch 0001+0002+0003 : 42.9 => 16.1%
> x86 VM:
>    HEAD                             : 32.8
>    patch 0001+0002           : 32.7 => 0%
>    patch 0001+0002+0003 : 28.0 => 17.1%
>

I tested these patches on my notebook - Lenovo T520 (x64) - on pi
calculation

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;

and I see about 3-5% of speedup

extra simply test shows

do $$ declare i int default 0; begin while i < 100000000 loop i := i + 1;
end loop; raise notice 'i=%', i;end $$;

2% speedup

I don't see 17% anywhere, but 3-5% is not bad.

patch 0001 has sense and can help with code structure
patch 0002 it is little bit against simplicity, but for PLpgSQL with blocks
structure it is correct.
patch 0003 has sense too

tested on Fedora 32 with gcc 10.1.1 and -O2 option

Regards

Pavel