On Mon, Mar 17, 2014 at 4:20 PM, Torsten Förtsch <torsten.foertsch@xxxxxxx> wrote: > On 17/03/14 21:42, Merlin Moncure wrote: >>> I can do it in plpgsql. But that would mean to accumulate the complete >>> > result in memory first, right? I need to avoid that. >> I would test that assumption. This is better handled in loop IMO. >> >> LOOP >> RETURN QUERY SELECT * FROM xx(); >> IF NOT found >> THEN >> RETURN; >> END IF; >> END LOOP; > > At least according to the manual it is stored in memory: > > <cite> > 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. > </cite> > > I didn't test that, though. The manual says describes the exact opposite of what you said you thought it does -- large result sets are paged out to disk, not stored in memory (this is a feature). CTEs use a similar tactic so it's a wash. The performance overhead of a tuplestore is probably not as bad as you think -- just test it out some simulated results and monitor performance. Either way, work_mem controls it. It's generally dangerous to crank work_mem to huge values but it's ok to set it temporarily via SET to huge values (say to 1GB) for a query particularly if you know that it's only getting issued by one caller at a time. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
