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

Hi Postgres people!

yes, I googled a lot for an answer to this question and found out that
it was asked
several times, but I could not find a sufficient answer. So here goes
my problem:

I am evaluating PostgreSQL at the moment. I got a table with about
4,500,000 rows - something I allways use for testing.

This is my table:

    public    adressen    id        -5    int8    8
    public    adressen    name    12    varchar    255
    public    adressen    strasse    12    varchar    255
    public    adressen    ort        12    varchar    255
    public    adressen    telefon    12    varchar    255

It has an index on name:
public    adressen_ixname    name    12    varchar    255

I use a plpgsql function to select data from the table.
Here is my function:

create or replace function fnc_selAdressByName(varchar, integer,
integer) returns setof adressen as '
declare

     rec public.adressen%ROWTYPE;

     pName alias for $1;
     pLimit alias for $2;
     pOffset alias for $3;

begin

     for rec in select * from public.adressen
                where name like pName
                order by name
                limit pLimit offset pOffset
     loop
         return next rec;
     end loop;

     return;
end

' language 'plpgsql';

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?????

Thx for any help!!!

Regards,
Ralf Hasemann

At 05:35 PM 7/12/04, Ralf Hasemann wrote:

>Hi Postgres people!
>
>yes, I googled a lot for an answer to this question and found out that it
>was asked
>several times, but I could not find a sufficient answer. So here goes my
>problem:
>
>I am evaluating PostgreSQL at the moment. I got a table with about
>4,500,000 rows - something I allways use for testing.
>
>This is my table:
>
>         public  adressen        id              -5      int8    8
>         public  adressen        name    12      varchar 255
>         public  adressen        strasse 12      varchar 255
>         public  adressen        ort             12      varchar 255
>         public  adressen        telefon 12      varchar 255
>
>It has an index on name:
>public  adressen_ixname name    12      varchar 255
>
>I use a plpgsql function to select data from the table.
>Here is my function:
>
>create or replace function fnc_selAdressByName(varchar, integer, integer)
>returns setof adressen as '
>declare
>
>     rec public.adressen%ROWTYPE;
>
>     pName alias for $1;
>     pLimit alias for $2;
>     pOffset alias for $3;
>
>begin
>
>     for rec in select * from public.adressen
>                where name like pName
>                order by name
>                limit pLimit offset pOffset
>     loop
>         return next rec;
>     end loop;
>
>     return;
>end
>
>' language 'plpgsql';
>
>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?????


If you change query from "SELECT ..."  to "EXPLAIN ANALYSE SELECT ..." does
output indicate use of index?

Does the system have tons of RAM?  Is it possible that the two queries were
run so close together that cache made the second one faster?  What happens
when you run the queries in the reverse order?  Try using different values
for pName during testing.

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.