Re: Use of indexes in plpgsql functions

"Graham Vickrage" <graham(at)digitalplanit(dot)com> 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