Search Postgresql Archives

Re: Slow index creation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux