On 05/09/11, Tomas Vondra (tv@xxxxxxxx) 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
