On 6 Září 2011, 0:27, Rory Campbell-Lange wrote: > On 05/09/11, Tomas Vondra (tv@xxxxxxxx) 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
