Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Ralf Hasemann <rhasemann(at)mac(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql functing does not use index....
Date: 2004-07-12 23:50:01
Message-ID: 20040712164530.O38194@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

pgsql-novice by date

Next:From: Barry GribbenDate: 2004-07-13 08:17:06
Subject: getting info out of a dump from postgresql
Previous:From: Frank BaxDate: 2004-07-12 23:27:50
Subject: Re: plpgsql functing does not use index....

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group