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

On 5 Září 2011, 23:07, Rory Campbell-Lange wrote:
> 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

Do I understand correctly that you compare a query with literal parameters
with a parametrized query wrapped in a plpgsql function?

Try to run it as a prepared query - I guess you'll get about the same run
time as with the function (i.e. over 100 seconds).

The problem with prepared statements is that when planning the query, the
parameter values are unknown - so the optimizer does not know selectivity
of the conditions etc. and uses "common" values to prepare a safe plan.
OTOH the literal parameters allow to optimize the plan according to the
actual parameter values.

Tomas