August 1st, 2007 by depesz | Tags: | 3 comments »
Did it help? If yes - maybe you can help me?

just couple of days ago i read about a new, great addition to postgresql 8.3 – “return query" in pl/pgsql.

what does it do?

in set returning functions, when you wanted to return multiple rows from a given query you had to:

FOR record IN SELECT ..... LOOP
    RETURN NEXT record;
END LOOP;

now, you can simply:

RETURN QUERY SELECT ...;

what's more – since RETURN QUERY doesn't terminate function (just like return next) you can:

RETURN QUERY SELECT something;
RETURN QUERY SELECT something else;

and then you'll get (more or less) “union all" of the queries.

additionally – return query is supposed to be faster then return next/loop.

so, let's test it.

i got brand new cvs-head-based pg, compiled and ran. then i created test-set:

create table test as select i from generate_series(1,1000) i;
CREATE OR REPLACE FUNCTION return_next_simple() RETURNS SETOF test AS $BODY$
declare
    temprec test%rowtype;
begin
    for temprec in SELECT * FROM test LOOP
        RETURN next temprec;
    END loop;
    RETURN;
end;
$BODY$ language plpgsql;
CREATE OR REPLACE FUNCTION return_query_simple() RETURNS SETOF test AS $BODY$
declare
    temprec test%rowtype;
begin
    RETURN query SELECT * FROM test;
    RETURN;
end;
$BODY$ language plpgsql;
CREATE OR REPLACE FUNCTION return_next_simple_ordered() RETURNS SETOF test AS $BODY$
declare
    temprec test%rowtype;
begin
    for temprec in SELECT * FROM test ORDER BY i desc LOOP
        RETURN next temprec;
    END loop;
    RETURN;
end;
$BODY$ language plpgsql;
CREATE OR REPLACE FUNCTION return_query_simple_ordered() RETURNS SETOF test AS $BODY$
declare
    temprec test%rowtype;
begin
    RETURN query SELECT * FROM test ORDER BY i desc;
    RETURN;
end;
$BODY$ language plpgsql;
CREATE type test_complex as ( "Schema" name, "Name" name, "Type" TEXT, "Owner" name );
CREATE OR REPLACE FUNCTION return_next_complex() RETURNS SETOF test_complex AS $BODY$
declare
    temprec test_complex;
begin
    for temprec in SELECT n.nspname as "Schema",
      c.relname as "Name",
      CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
      r.rolname as "Owner"
    FROM pg_catalog.pg_class c
         JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','v','S','')
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 1,2
    LOOP
        RETURN next temprec;
    END loop;
    RETURN;
end;
$BODY$ language plpgsql;
CREATE OR REPLACE FUNCTION return_query_complex() RETURNS SETOF test_complex AS $BODY$
declare
    temprec test_complex;
begin
    RETURN query SELECT n.nspname as "Schema",
                   c.relname as "Name",
                   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
                   r.rolname as "Owner"
                 FROM pg_catalog.pg_class c
                      JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
                      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                 WHERE c.relkind IN ('r','v','S','')
                   AND pg_catalog.pg_table_is_visible(c.oid)
                 ORDER BY 1,2;
    RETURN;
end;
$BODY$ language plpgsql;

explanation:

  • new table for simple test – select * from table, or select * from table order by field
  • custom type for returning recordsets with more then 1 field per set

i have chosen queries that were fast – to make it possible to check how much burden does return next/return query really do.

then i wrote small perl program which did the test.

in this test i ran several hundred thousands of queries to see which way is faster.

each type of query (select * from table; select * from table order by field; complex-query-with-joins) was executed in 4 different modes:

  • simple select …
  • execute of prepared plan with given select (this plan was prepared only once)
  • select * from function() where function used loop/return next approach
  • select * from function() where function used return query approach

results:

Test on simplest query: select * from test
test: min max sum iter/s
next 0.00390 0.04018 408.59155 244.74319
query 0.00350 0.03075 365.67770 273.46486
sql-prep 0.00334 0.03152 350.42446 285.36821
sql 0.00318 0.06028 334.28738 299.14380
Test on ordered query: select * from test order by i desc
test: min max sum iter/s
next 0.00458 0.03273 480.10789 208.28652
query 0.00419 0.03174 441.19612 226.65657
sql-prep 0.00402 0.03105 422.16855 236.87221
sql 0.00390 0.03116 411.16180 243.21326
Test on complex query: (\d with all schemas, 70 entries returned)
test: min max sum iter/s
sql 0.00264 0.03053 2857.79664 349.91993
next 0.00226 0.03032 2413.23163 414.38210
query 0.00215 0.05947 2290.68159 436.55129
sql-prep 0.00210 0.06050 2275.77135 439.41146

results quite surprised me.

first of all – return next is not that slow?! actually – it is faster to use return next then plain old sql in case we use complex queries (put in here your definition of complex query – you can see tested queries in mentioned perl program).

then. return query was faster. how much faster – between 5 and 12%. this, plus the fact that code is more readable makes it a great addition to plpgsql.

what was really surprising is that when dealing with simples possible queries (single table, no where) pumping sql's is the fastest way. it's even faster then using prepare/execute?! (remember: prepare was only called once!)

  1. 3 comments

  2. # Alejandro
    Oct 23, 2007

    Great Page!
    Really clear on the subject also very informative.

  1. 2 Trackback(s)

  2. May 3, 2008: select * from depesz;» Blog Archive » Waiting for 8.4 - RETURN QUERY EXECUTE and cursor_tuple_fraction
  3. May 14, 2010: Postgres Return Query bug | Npgsql Blog

Leave a comment