PostgreSQL fast query is too slow as function

Hi Ho!

The plain query runs for about 8.28 minutes.
But, when the same query is put inside a function as follows:

--- 8< ---
create or replace function get_outgoing_transactions(
area char(3),
start_at timestamp with time zone,
end_at timestamp with time zone) returns setof record as
$$
begin
return query (
-- the plain query --
);
end;
$$ language plpgsql;
--- 8< ---

and called as follows:

--- 8< ---
select *
from get_outgoing_transactions('sew'
, '2008-05-30 00:00:00'
, '2008-10-30 00:00:00'
)
as (production_order_id character varying(15)
, item_id integer
, tag_id character varying(15)
, color_abbrv_description character varying(15)
, size_id character varying(10)
, prev_grade character varying(10)
, grade character varying(10)
, audit_ts timestamp with time zone
, from_area char(3)
, into_area char(3)
)
--- 8< ---

it runs for about 21.50 minutes.

I have read this blog: http://blog.endpoint.com/2008/12/why-is-my-function-slow.html, and therefore, I ran each case two times. The running times that I write above were taken from the second run of each case, which is always shorter than the first run.

As described in the blog, I also have tried to find out whether or not a different query plan is used as follows:

--- 8< ---
prepare foobar(char(3)
, timestamp with time zone
, timestamp with time zone) as
-- the plain query --
;

explain execute foobar('sew'
, '2008-05-30 00:00:00'
, '2008-10-30 00:00:00');
--- 8< ---

The query plan is just the same with `explain -- the plain query --' with a difference that the plain query has castings on the plain parameters.

I thought the bottle neck was in the use of `returns setof record'.
But, changing it to just return the table does not change the situtation.

Any idea as to how I should attack this problem?

Thank you.

Best regards,
Eus (FSF member #4445)

In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices.

Join free software movement today! It is free as in freedom, not as in free beer!

Join: http://www.fsf.org/jf?referrer=4445