pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why indexes are not used when scanning from functions?
2+ messages / 2 participants
[nested] [flat]

* Why indexes are not used when scanning from functions?
@ 2001-05-18 23:30  "Ловпаче Айдамир" <aidanet@yandex.ru>
  0 siblings, 1 reply; 2+ messages in thread

From: "Ловпаче Айдамир" @ 2001-05-18 23:30 UTC (permalink / raw)
  To: pgsql-sql

The table (script followed) contains more than 200000 records. 
When I directly selecting records by the single select statement
   
   select fio from patient_temp where fio like 'something%'; 

Postgres uses the index i have created and quickly returns the results. But if that query included into the function, slowest sequent scan starts. Is it a normal postgres behavior - not using indexes in functions? Or how to make indexes usable in functions? 
I have tried PL/pgSQL, and have got the same result.

create table patient_temp(
	code serial primary key,	
	fio varchar(80)
);

create index fio_patient_temp_ind on patient_temp(fio);

create function get_fio1(text) returns varchar
as 'select fio from patient_temp where fio like $1::text || \'%\';'  
language 'sql';

				Aidamir Lovpache <aidanet@yandex.ru>





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Why indexes are not used when scanning from functions?
@ 2001-05-23 18:28  Tom Lane <tgl@sss.pgh.pa.us>
  parent: "Ловпаче Айдамир" <aidanet@yandex.ru>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-05-23 18:28 UTC (permalink / raw)
  To: "Ловпаче Айдамир" <aidanet@yandex.ru>; +Cc: pgsql-sql

=?KOI8-R?B?IuzP19DB3sUg4crEwc3J0iI=?= <aidanet@yandex.ru> writes:
> create function get_fio1(text) returns varchar
> as 'select fio from patient_temp where fio like $1::text || \'%\';'  
> language 'sql';

You won't get an indexscan for this because the LIKE pattern is not
a constant at planning time, and so the planner cannot extract indexscan
bounds from it.

In 7.1 it's possible to obtain the desired result in plpgsql, by using
EXECUTE.  You'd need to substitute the pattern into the query string
as a literal constant, not as a plpgsql variable.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-05-23 18:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-18 23:30 Why indexes are not used when scanning from functions? "Ловпаче Айдамир" <aidanet@yandex.ru>
2001-05-23 18:28 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox