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 >
