Re: Slow index creation

On 2/16/21 12:30 PM, 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;
>
> 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;
>
> and a large table t (100M+ records) with columns a, b, c and geom running
> on PG 11, on spinning disks with 64GB memory and 28 cores.
>
> When I create a simple geom index with CREATE INDEX ON t USING gist(geom)
> it finishes in about an hour, but when I create a partial index using
> these 2 functions
> CREATE INDEX ON t USING gist(geom) WHERE bar(foo(a,b,c),geom)<12 it takes
> over 20 hours...
>
> Is that because I'm using functions in the WHERE clause, or because my
> CASE lists are quite long, or both?

How long does SELECT a, b, c, foo(a, b, c) from blarg; take?
Ditto SELECT a, b, c, foo(a, b, c), bar(foo(a, b, c)) from blarg;
Ditto SELECT a, b, c, foo(a, b, c) from blarg where bar(foo(a, b, c)) < 12;

That'll narrow the problem.

> Is there any way to speed up that index creation? Is upgrading to a newer
> postgres a viable option (so the JITTER can speed up the functions)?
>
> Paul

--
Angular momentum makes the world go 'round.