I have a PL/pgSQL function that I want to call within a query, but the function is fairly expensive to execute so I only want it executed once within the query. However the planner seems to reorganize my query so that it calls the function for every row.
We were previously on Pg 9.6 and this wasn't a problem then. But now that we have upgraded to Pg 13, the behaviour has changed.
I thought that marking the function as STABLE would mean that the function would only be called once within a query, but this doesn't seem to be the case. (Note: the function isn't IMMUTABLE). I've also tried increasing the cost of the function, but this doesn't make any difference.
From looking at previous posts I discovered that putting "offset 0" on the function call in a "with" clause means that it only gets called once (because then the Common Table _expression_ isn't combined with the rest of the query).
This does work, however it seems rather a kludge (and might not work in future versions of PostgreSQL).
There must be a "proper" way to get the planner to call a function only once.
Postgres version: PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
Here's a simple test case that demonstrates the issue:
create or replace function test_caching(v integer)
returns text
as
$BODY$
begin
raise NOTICE 'In test_caching(%) function', v;
return 'Test';
end
$BODY$
LANGUAGE plpgsql STABLE
COST 500;
select n, test_caching(7) from generate_series(1, 10) n;
-- test_caching(...) is called 10 times
with tc as (
select test_caching(7)
)
select n, tc.test_caching
from tc
cross join generate_series(1, 10) n;
-- test_caching(...) is called 10 times
-- (in Pg 9.6, test_caching(...) is only called once)
with tc as (
select test_caching(7) offset 0
)
select n, tc.test_caching
from tc
cross join generate_series(1, 10) n;
-- test_caching(...) is called once
returns text
as
$BODY$
begin
raise NOTICE 'In test_caching(%) function', v;
return 'Test';
end
$BODY$
LANGUAGE plpgsql STABLE
COST 500;
select n, test_caching(7) from generate_series(1, 10) n;
-- test_caching(...) is called 10 times
with tc as (
select test_caching(7)
)
select n, tc.test_caching
from tc
cross join generate_series(1, 10) n;
-- test_caching(...) is called 10 times
-- (in Pg 9.6, test_caching(...) is only called once)
with tc as (
select test_caching(7) offset 0
)
select n, tc.test_caching
from tc
cross join generate_series(1, 10) n;
-- test_caching(...) is called once
-- works, but a kludge
Steve
-- Steve Pritchard
Database Developer
British Trust for Ornithology, The Nunnery, Thetford, Norfolk IP24 2PU, UK
Tel: +44 (0)1842 750050, fax: +44 (0)1842 750030
Registered Charity No 216652 (England & Wales) No SC039193 (Scotland)
Company Limited by Guarantee No 357284 (England & Wales)
Tel: +44 (0)1842 750050, fax: +44 (0)1842 750030
Registered Charity No 216652 (England & Wales) No SC039193 (Scotland)
Company Limited by Guarantee No 357284 (England & Wales)
