Received: from sss.pgh.pa.us (sss.pgh.pa.us [216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4NISeA03607 for ; Wed, 23 May 2001 14:28:40 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f4NISIR16265; Wed, 23 May 2001 14:28:23 -0400 (EDT) To: =?KOI8-R?B?IuzP19DB3sUg4crEwc3J0iI=?= cc: pgsql-sql@postgresql.org Subject: Re: Why indexes are not used when scanning from functions? In-reply-to: <3B05B07C.000001.06516@camay.yandex.ru> References: <3B05B07C.000001.06516@camay.yandex.ru> Comments: In-reply-to =?KOI8-R?B?IuzP19DB3sUg4crEwc3J0iI=?= message dated "Sat, 19 May 2001 03:30:04 +0400" Date: Wed, 23 May 2001 14:28:18 -0400 Message-ID: <16262.990642498@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200105/255 X-Sequence-Number: 3083 =?KOI8-R?B?IuzP19DB3sUg4crEwc3J0iI=?= 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