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?
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