Hi 2018-07-27 11:24 GMT+02:00 Brahmam Eswar <brahmam1...@gmail.com>:
> 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. >
