plpgsql functing does not use index....
От | Ralf Hasemann |
---|---|
Тема | plpgsql functing does not use index.... |
Дата | |
Msg-id | 55B83304-D44B-11D8-8DB9-000393D76D50@mac.com обсуждение исходный текст |
Ответы |
Re: plpgsql functing does not use index....
Re: plpgsql functing does not use index.... |
Список | 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
В списке pgsql-novice по дате отправления: