Re: Slow index creation - Mailing list pgsql-general

On Tue, Feb 16, 2021 at 07:30:23PM +0100, Paul van der Linden wrote:
> Hi,
> I have 2 functions:
> CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS
> $func$
> DECLARE
>     retVal text;
> BEGIN
>     SELECT
>       CASE
>         WHEN a='v1' AND b='b1' THEN 'r1'
>         WHEN a='v1' THEN 'r2'
>         ... snip long list containing various tests on a,b and c
>         WHEN a='v50' THEN 'r50'
>       END INTO retval;
>     RETURN retVal;
> END
> $func$ LANGUAGE PLPGSQL IMMUTABLE;

If this function was converted to SQL function it could be faster, as it
could be inlined.

> CREATE FUNCTION bar(r text, geom geometry) RETURNS int AS
> $func$
> DECLARE
>     retVal int;
> BEGIN
>     SELECT
>       CASE
>         WHEN r='r1' AND st_area(geom)>100 THEN 1
>         WHEN r='r1' THEN 2
>         ... snip long list containing various tests on r and st_area(geom)
>         WHEN r='r50' THEN 25
>       END INTO retval;
>     RETURN retVal;
> END
> $func$ LANGUAGE PLPGSQL IMMUTABLE;

First thing that I notice is that it seems (from the code and comment)
that you run st_area(geom) multiple times.

If that's really the case - why don't you cache it in some variable?

declare
    v_area float := st_area( geom );
begin
...

and then use v_area instead of st_area(geom)

depesz