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

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

Well, my point was that the queries wrapped in functions are executed just
like prepared statements. And because prepared queries do not use
parameter values to optimize the plan, the result may be worse compared to
queries with literal parameters. So I was not expecting an improvement, I
was merely trying to show the problem.

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

I've posted the plan here: http://explain.depesz.com/s/uYX

Yes, the nested loop is the problem. One of the problems is that the join
condition - can you rewrite this

AND r.d_date || '-' || r.n_session || '-' || u.n_id IN
(SELECT
d_date || '-' || n_session || '-' || n_person
FROM
leave_association
WHERE
d_date >= in_date_from
AND d_date <= in_date_to
) -- i.e. leave where the person normally works

like this

AND EXISTS
(SELECT
1
FROM
leave_association
WHERE
d_date >= in_date_from
AND d_date <= in_date_to
AND d_date = r.d_date
AND n_session = r.n_session
AND n_person = u.n_id
) -- i.e. leave where the person normally works

and then do the same for the NOT IN subquery (=> NOT EXISTS). I think it
should return the same results, plus it might use indexes on the
leave_association. That was not possible because of the join condition.

Tomas