On 05/09/11, Rory Campbell-Lange (rory@xxxxxxxxxxxxxxxxxx) wrote: > 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. ... > > 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. I added in more filtering conditions to the clause at line 72 and the prepared statement dropped in runtime to 24043.902 ms. Unfortunately the function ran slower -- 47957.796 -- but even that is a 50% improvement. Thanks very much for your help. Regards Rory -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
