Do function calls the cached?

From: Daniel Caldeweyher <dcalde(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Do function calls the cached?
Date: 2016-08-30 20:15:41
Message-ID: CADVnD3BCbh4s4P=P1Mzj87RcvUkdHDxFnc-n5r8pe=Oym3czAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have an expensive function that does a lot of regex and other text
analysis. This function relies on several columns of my table and for
efficiency/code reuse also has several OUT arguments:

CREATE OR REPLACE FUNCTION extract_keywords(IN l large_table, OUT a text,
OUT b text, OUT c text)
RETURNS record AS
$BODY$
select l.a ~* 'lots of regex' as a, l.b ~* 'lots of regex' as b, l.c ~*
'lots of regex' as c
from large_table l
$BODY$
LANGUAGE sql STABLE;

I want to use this function in a view and since I only want the function to
be called once I created my view as such:

create view with_keywords as
select x,y,z, (extract_keywords(l.*)).*
from large_table;

However when I then inspect the generated SQL for the view in pgadmin I get:

CREATE VIEW with_keywords AS
SELECT x,y,z,
(extract_keywords(l.*)).a AS a,
(extract_keywords(l.*)).b AS b,
(extract_keywords(l.*)).c AS c
FROM large_table l;

Does this mean the function gets called three time? The answer in
http://stackoverflow.com/questions/20718499/does-postgresql-cache-function-calls
suggests that no function call, not even for stable functions, ever gets
cached. It also states that I should penalize the function with a high
cost. But I don't see how this has any benefit in this case other than mess
up the rest of my query plan.

Thanks,
Daniel

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2016-08-30 20:46:45 Re: Do function calls the cached?
Previous Message Kenneth Marshall 2016-08-30 18:17:05 Re: Clustered index to preserve data locality in a multitenant application?