Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBFMs2619961 for ; Fri, 15 Dec 2000 17:54:02 -0500 (EST) (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.1/8.11.1) with ESMTP id eBFMsSL13997; Fri, 15 Dec 2000 17:54:28 -0500 (EST) To: "Graham Vickrage" cc: pgsql-sql@postgresql.org Subject: Re: Use of indexes in plpgsql functions In-reply-to: References: Comments: In-reply-to "Graham Vickrage" message dated "Fri, 15 Dec 2000 22:05:01 +0000" Date: Fri, 15 Dec 2000 17:54:28 -0500 Message-ID: <13994.976920868@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200012/178 X-Sequence-Number: 512 "Graham Vickrage" writes: >> CREATE FUNCTION get_url_hits (varchar, int4) RETURNS int4 AS ' >> DECLARE >> num INT4; >> BEGIN >> SELECT count(*) INTO num FROM statistics WHERE url = $1 and >> website_id = $2; > [ is slow ] A possible gotcha is if the url and website_id columns are declared as something other than varchar and int4 respectively. The planner's not very smart about optimizing cross-datatype comparisons into indexscans. When you write out a query with constants you are protected from this because the constants have their types adjusted, but when you write "url = $1" you'd best make sure $1 is declared exactly the same way as url. 7.1 fixes some instances of this gotcha, but not all of 'em IIRC. regards, tom lane