Returning multiple rows from store functions using "RETURNS TABLE" and RETURN QUERY. The results set may have more than 50k records. Does it give any performance issues related to memory? if yes how to avoid it
This command uses tuple store - the result is stored in memory to work_mem size, and later is materialized (stored to temporary file).
Probably worst effect is invisibility of embedded query for planner. The default estimation of SRF (set returning function) is 1000 rows. If you run 50K rows, the estimation will be really off, and the plan of query can be strongly suboptimal.
The best practice is not using similar functions. Functions should not to supply views. It is antipattern with more than one possible performance issue.
regards
Pavel
CREATE OR REPLACE FUNCTION funcq(COL1 character varying)
)
RETURNS TABLE
( a VARCHAR,
b VARCHAR,
c varchar)
AS $$
BEGIN
RETURN QUERY SELECT a,b,c from table1 where C= COL1;