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

plpgsql functing does not use index....

From: Ralf Hasemann <rhasemann(at)mac(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: plpgsql functing does not use index....
Date: 2004-07-12 21:35:03
Message-ID: 55B83304-D44B-11D8-8DB9-000393D76D50@mac.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


Responses

pgsql-novice by date

Next:From: Frank BaxDate: 2004-07-12 23:27:50
Subject: Re: plpgsql functing does not use index....
Previous:From: Andy HarrisonDate: 2004-07-12 18:02:14
Subject: Re: using 'count' to show number of dupes

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