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

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

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql functing does not use index....
Date: 2004-07-12 23:27:50
Message-ID: 5.2.1.1.0.20040712191852.02728b20@pop6.sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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. 


In response to

pgsql-novice by date

Next:From: Stephan SzaboDate: 2004-07-12 23:50:01
Subject: Re: plpgsql functing does not use index....
Previous:From: Ralf HasemannDate: 2004-07-12 21:35:03
Subject: plpgsql functing does not use index....

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