Thread: Do function calls the cached?
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
select x,y,z, (extract_keywords(l.*)).*
[...]
Does this mean the function gets called three time?
Yes.
(function_call(...)).*
syntax is problematic. You should avoid it via one of two options.
LATERAL (new way, preferred)
or
CTE (old way)
In the CTE version you make the call in the CTE but do "(col).*" in the main query. This way the function is only called once to generate a composite output, then the composite output is exploded.
With LATERAL the system is smart enough to do it the right way.
David J.
Thanks David,
Lateral did the trick:
CREATE VIEW with_keywords AS
SELECT x,y,z, keywords.a, keywords.b, keywords.c
FROM large_table l, LATERAL extract_keywords(l.*) keywords(a,b,c)
Regards,
Daniel
On Wed, Aug 31, 2016 at 6:46 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
select x,y,z, (extract_keywords(l.*)).*[...]Does this mean the function gets called three time?Yes.(function_call(...)).*syntax is problematic. You should avoid it via one of two options.LATERAL (new way, preferred)orCTE (old way)In the CTE version you make the call in the CTE but do "(col).*" in the main query. This way the function is only called once to generate a composite output, then the composite output is exploded.With LATERAL the system is smart enough to do it the right way.David J.