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.
