Re: plpgsql functing does not use index....

On Mon, 12 Jul 2004, Ralf Hasemann wrote:

> I call the function with: select * from
> fnc_selAdressByName('Hasemann%', 5, 0);
> The request takes about 22 sec.
>
> When I execute the query of the function directly:
> select * from public.adressen where name like 'Hasemann%' order by
> name limit 5 offset 0
> the request takes about 0.058 sec.
>
> So I get the idea that the query uesn in the plpgsql function did not
> use the adressen_ixname index.
>
> Why????? What can I do to make it use the index?????

Because the query is basically planned without knowledge of the arguments
because it's saved for later calls. While the index scan works for
'Hasemann%', it doesn't for '%foo' for example. In addition, it won't know
what limit and offset you're going to use. If you want to force it to plan
with the arguments passed, you can use the plpgsql FOR ... IN EXECUTE
querystring version.