Re: Return Multiple Rows from Store Function

Hi

2018-07-27 11:24 GMT+02:00 Brahmam Eswar <brahmam1234(at)gmail(dot)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.
>