Re: Return Multiple Rows from Store Function

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

2018-07-27 11:24 GMT+02:00 Brahmam Eswar <brahmam1234@xxxxxxxxx>:

Hi ,

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;

END;

$$

LANGUAGE plpgsql;

--

Thanks & Regards,
Brahmeswara Rao J.