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

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.