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

On Tue, 26 May 2020 at 09:06, Amit Khandekar <amitdkhan(dot)pg(at)gmail(dot)com> wrote:
>
> On Sat, 23 May 2020 at 23:24, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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%