Sorry for the premature send on that last email. Here is the full one:
Thanks for your reply Tom. I've found that the culprit is the function parentContainers(), which recurses up a folder structure and looks like this:
create function parentContainers(numeric) returns setof numeric
as '
select parentContainers( (select container_id from container where id = $1 ) )
union
select id from container where id = $1
' language sql stable returns null on null input;
It is declared stable, but I know that STABLE is just planner hint, so it doesn't guarantee that it will only get called once. If I replace the function call with the two values this function returns, I get < 1 ms runtime on all versions of pg. So there is data to support the statement that we were relying on planner luck before and that luck has run out.
What is the best practice to ensure a stable function only gets called once? Should I use a CTE to cache the result? Is there a better way?
Thanks in advance,