Postgres Pro
Downloads
Home   >   mailing lists

Re: Want function to be called only once in query - Mailing list pgsql-performance

From Jim Mlodgenski
Subject Re: Want function to be called only once in query
Date
Msg-id CAB_5SRee1qAo9U3smCMxezHWi-w+1=1sHk_q2Hcnt5LbgxO9jA@mail.gmail.com
Whole thread Raw
In response to Want function to be called only once in query  (Steve Pritchard <steve.pritchard@bto.org>)
Responses Re: Want function to be called only once in query  (Steve Pritchard <steve.pritchard@bto.org>)
List pgsql-performance
Tree view 
On Thu, Sep 16, 2021 at 4:51 AM Steve Pritchard <steve.pritchard@bto.org> wrote:
>
> I have a PL/pgSQL function that I want to call within a query, but the function is fairly expensive to execute so I
onlywant it executed once within the query. However the planner seems to reorganize my query so that it calls the
functionfor 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
haschanged.
 
>

The behavior for planning a CTE changed in PG12.

> There must be a "proper" way to get the planner to call a function only once.
>
Add the MATERIALIZED keyword to the WITH statement

pgsql-performance by date:

Previous
From: Steve Pritchard
Date:
Subject: Want function to be called only once in query
Next
From: Steve Pritchard
Date:
Subject: Re: Want function to be called only once in query