Table valued vs OUT function's performance in postgresql

Hello fellow postgresql experts,

I was wandering about the opportunity to use table-valued functions vs functions returning output sets.  From your experience, which one,  assuming all indexes are in place, presents the best performance regarding :

> Concurrent accesses.  
> Handling by the optimizer
> CPU and IO execution usage.
> Locking

I would appreciate you enlighten me on the above.  Thank you for your help.
LVL 23
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAsked:
Who is Participating?
 
earth man2Connect With a Mentor Commented:
From the docs...
Note: The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance might be poor: data will be written to disk to avoid memory exhaustion, but the function itself will not return until the entire result set has been generated. A future version of PL/pgSQL might allow users to define set-returning functions that do not have this limitation. Currently, the point at which data begins being written to disk is controlled by the work_mem configuration variable.
 Administrators who have sufficient memory to store larger result sets
 in memory should consider increasing this parameter.

Returns table an returns setof are essentially equivalent.
If you have very large datasets then you are better of using libpq functions or cursors.
0
All Courses

From novice to tech pro — start learning today.