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

On 05/09/11, Tomas Vondra (tv(at)fuzzy(dot)cz) wrote:
> On 5 Zá??í 2011, 23:07, Rory Campbell-Lange wrote:
...
> > 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.
...
> > 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?

Yes! Certainly I need to make the function perform more quickly.

> 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 prepared query runs in almost exactly the same time as the function,
but thanks for the suggestion. A very useful aspect of it is that I was
able to get the EXPLAIN output which I guess gives a fairly good picture
of the plan used for the function.

The explain output is here:
http://campbell-lange.net/media/files/explain.txt.html

I'm inexperienced in reading EXPLAIN output, but it looks like the
Nested Loop Semi Join at line 72 is running very slowly.

> 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.

Thank you very much for the helpful explanation.

Regards
Rory