Re: Use of indexes in plpgsql functions - Mailing list pgsql-sql

From Tom Lane
Subject Re: Use of indexes in plpgsql functions
Date
Msg-id 13994.976920868@sss.pgh.pa.us
Whole thread Raw
In response to Use of indexes in plpgsql functions  ("Graham Vickrage" <graham@digitalplanit.com>)
List pgsql-sql
"Graham Vickrage" <graham@digitalplanit.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


pgsql-sql by date:

Previous
From: "Graham Vickrage"
Date:
Subject: Use of indexes in plpgsql functions
Next
From: Alvar Freude
Date:
Subject: Re: How to represent a tree-structure in a relationaldatabase