Re: function is quick for one row but super slow on more than 5..
Hi All,
the following takes about 2 seconds to run:
select route,not_in_route2(route) from ksanrt
limit 5,
but if i limit it to anything greater than 5 it takes forever to come
back. Forever as in I always cancel the query.
Any reasons why?
Show quoted text
On 7/19/06, Rhys Stewart <rhys.stewart@gmail.com> wrote:
well i started by creating a table with the data i needed, instead of
working on the big table....that helped a whole lot.thanks.
On 7/18/06, Michael Fuhr <mike@fuhr.org> wrote:On Tue, Jul 18, 2006 at 08:11:40AM -0500, Rhys Stewart wrote:
i created a function and it takes a long time to run. I was testing it
as a wrote it and to the first drop table it takes just short of a
second. however when the rest of the code is added on, it takes
upwards of 5 minutes. Not the most appropriate thing. Are there any
tips out there for making functions go faster?Find out what parts of the function are slow. Have you used RAISE
to display the function's progress? Have you executed any of the
queries by hand to see how fast they run? Have you used EXPLAIN
ANALYZE to see if you could benefit from rewriting a query, adding
indexes, or tuning configuration settings?The UPDATE statement with the ORs and regular expression matches
looks like it might be slow. Is it?--
Michael Fuhr