Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.10.1/8.10.1) with ESMTP id e77ElsG15837 for ; Mon, 7 Aug 2000 10:47:54 -0400 (EDT) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Mon, 7 Aug 2000 09:47:53 -0500 (CDT) Date: Mon, 7 Aug 2000 09:47:53 -0500 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: Functions too slow, even with iscachable? Message-ID: <20000807094753.A26007@rice.edu> Mail-Followup-To: pgsql-sql@postgresql.org References: <20000807171110.A31838@pintoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <20000807171110.A31838@pintoo.com>; from angch@pintoo.com on Mon, Aug 07, 2000 at 05:11:10PM +0800 X-Archive-Number: 200008/52 On Mon, Aug 07, 2000 at 05:11:10PM +0800, Ang Chin Han wrote: > 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); Is this a cut-n-paste error, or did you drop half a term from your WHERE clause? "survey_id =" seems to be missing in the functional form. I din't see how that would make it run slowly, however. Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005