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 <dep...@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 > ... > > and then use v_area instead of st_area(geom) > > depesz >
