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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

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


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.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Mon, 12 Jul 2004, Ralf Hasemann wrote:

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster