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