Received: from sss2.sss.pgh.pa.us (root@sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e77EwYG19834 for ; Mon, 7 Aug 2000 10:58:34 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA06061; Mon, 7 Aug 2000 10:58:28 -0400 (EDT) To: Ang Chin Han cc: pgsql-sql@postgresql.org Subject: Re: Functions too slow, even with iscachable? In-reply-to: <20000807171110.A31838@pintoo.com> References: <20000807171110.A31838@pintoo.com> Comments: In-reply-to Ang Chin Han message dated "Mon, 07 Aug 2000 17:11:10 +0800" Date: Mon, 07 Aug 2000 10:58:27 -0400 Message-ID: <6058.965660307@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200008/53 Ang Chin Han writes: > I have a query which runs fast: > SELECT passwd FROM ticket WHERE name = ticket2name('test-006kdt') AND > survey_id = ticket2survey_id('test-006kdt'); > But slows down to a crawl when I wrapped it in a function: > CREATE FUNCTION ticket2passwd(text) RETURNS text AS > 'SELECT passwd FROM ticket WHERE name = ticket2name($1) AND > ticket2survey_id($1)' LANGUAGE 'sql' WITH (iscachable); (I assume the lack of "survey_id =" here is just a cut-and-paste error?) I think what you're getting bit by is that the optimizer doesn't recognize "var = function(param)" as being a potential indexscan clause. Does EXPLAIN show that the first query is producing an indexscan plan? I have not tried it, but I think you could get around this problem in plpgsql, along the lines of tmp1 = ticket2name($1); tmp2 = ticket2survey_id($1); SELECT passwd FROM ticket WHERE name = tmp1 AND survey_id = tmp2; since the tmp vars will look like params to the optimizer and "var = param" is indexable. Looks like we need to teach the optimizer that expressions involving params can be treated like simple params for the purposes of optimization. regards, tom lane