Re: Important speed difference between a query and a

On Thu, 24 Apr 2003, Frederic Jolliton wrote:

> > (PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4)
> >
> > I've a curious performance problem with a function returning set of
> > rows. The query alone is very fast, but not when called from the
> > function.
> >
> > To "emulate" a parametred view, I created a function as follow:
> >
> > CREATE FUNCTION get_info (integer) RETURNS SETOF type_get_info
> > AS '...' <- here the query show below, where 'LIMIT $1' is used instead of 'LIMIT 10'
> > LANGUAGE sql;
>
> Setting enable_seqscan to off give same result speed between the query
> and the function !
>
> So, the query in the function is not using index but the exact same
> query alone does !
>
> Is there an explanation ?

My guess is that limit $1 is assuming a larger number of rows when
planning the queries, large enough that it expects seqscan to be better
(assuming the limit is what it expects). It's probably not going to plan
that query each time the function is called so it's not going to know
whether you're calling with a small number (index scan may be better) or a
large number (seq scan may be better). For example, if you sent 100000,
the index scan might be a loser.

Perhaps plpgsql with EXECUTE would work better for that, although it's
likely to have some general overhead.