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?

On 7/19/06, Rhys Stewart <rhys(dot)stewart(at)gmail(dot)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(at)fuhr(dot)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
> >
>