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
