Hi, Thanks for your prompt response. Appreciate your help.
Thanks and best regards, bocap > Hi > > 2016-03-16 15:58 GMT+01:00 Dang Minh Huong <kakalo...@gmail.com>: >> Hi, >> >> Why does log temp files are created twice when query is executed in PL/pgSQL >> function? >> Would you please explain it to me? > > PL/pgSQL materialize result internally. > > Usually PostgreSQL operations are executed row by row. But some SRF functions > like PLpgSQL functions doesn't support this mode, and returns tuplestore - > materialized result. > > Using this technique is comfortable, but with some performance risks. > Unfortunately, you cannot to change this behave. Not in PL/pgSQL. > > You can write C function with same functionality but with row by row > returning result mode. It is not possible in PL/pgSQL. > > On other hand - you can try to increase work_mem (if your server has enough > RAM). Materialization are done when available memory (controlled by work_mem) > is too less. > > You can try > > SET work_mem to '20MB'; > SELECT test_tempfiles(); > > Regards > > Pavel > >> >> As below test result. Log temp files are created twice when SELECT statement >> is put >> into a PL/pgSQL function. It led a little of performance degradation. >> Is there any way to define PL/pgSQL function to avoid this issue? >> # I am using PostgreSQL 9.3.9 >> >> my test results >> ----- >> [postgres@test]$ psql -c "select test_tempfiles();" > /dev/null >> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp2223.0", size 3244032 >> CONTEXT: PL/pgSQL function test_cursor() line 3 at RETURN QUERY >> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp23223.1", size 2828062 >> LOG: duration: 421.426 ms statement: select test_tempfiles(); >> >> [postgres@test]$ psql -c "select name from testtbl order by id" > /dev/null >> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp25586.0", size 2850816 >> LOG: duration: 389.054 ms statement: select random from testtbl order by >> random >> ----- >> >> test_tempfiles() function is defined as below >> ----- >> CREATE OR REPLACE FUNCTION public.test_tempfiles() >> RETURNS TABLE(name text) >> LANGUAGE plpgsql >> AS >> $function$ >> begin >> return query execute "select name from testtbl order by id "; >> end; >> $function$ >> ----- >> >> Thanks and best regrards, >> bocap >> >> >> >> >> >> >> >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >
