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@depesz.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 ...