Query runs in 335ms; function in 100,239ms : date problem?

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Query runs in 335ms; function in 100,239ms : date problem?
Date: 2011-09-05 21:07:54
Message-ID: 20110905210754.GB13235@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a function wrapping a (fairly complex) query.

The query itself runs in about a 1/3rd of a second. When running the
query as a 'RETURN QUERY' function on Postgres 8.4, the function runs in
over 100 seconds, about 300 times slower.

The function takes 3 input parameters: 2 dates and a boolean. The dates
(in_date_from, in_date_to) are used several times in the function.

When I replace the two parameters in the body of the query with, for
instance date'2011-05-01' and date'2011-08-01', the function operates
almost as speedily as the straight query.

I would be grateful to know how to work around this date problem.

As the function is rather large I have taken the liberty of posting it
here:
http://campbell-lange.net/media/files/fn_report_pers_leave.sql.html

Rory

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomas Vondra 2011-09-05 21:23:13 Re: Query runs in 335ms; function in 100,239ms : date problem?
Previous Message Bruce Momjian 2011-09-05 21:04:32 Re: [GENERAL] pg_upgrade problem