Re: Slow index creation

The st_area calculation is done mostly once or sometimes twice for each
geom, and I suspect that can't explain the factor 20 slower.
Creating an index with only one st_area calculation is also done rather
quickly.

On Wed, Feb 17, 2021 at 7:48 PM hubert depesz lubaczewski <depesz(at)depesz(dot)com>
wrote:

> 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
>