Re: Fast statement but slow function

Hallo,

On Wed, May 08, 2002 at 12:53:19PM -0400, Tom Lane wrote:
> Thomas Beutin <tyrone@laokoon.IN-Berlin.DE> writes:
> > Is this a bug in my function or in my mind or in postgres?
> > Are functions using indexes?
>
> The planning context is different because the planner cannot see a
> specific constant in the WHERE clause, only a parameter placeholder.
> This might affect the choice of plan --- but without knowing what
> indexes you have and what choices are being made, it's hard to say
> much.
What can i do to trace the problem and find the bottleneck?

> One question worth asking is whether you've declared the type of
> the parameter to agree with the type of the column it's being
> compared to.
This is my function declaration:
CREATE FUNCTION "o_l4_a_id_count" (character) RETURNS integer AS
'SELECT count(a_id) FROM (SELECT DISTINCT a_id
FROM o_kat_ausst AS k
WHERE k.l4_id = $1
UNION
SELECT DISTINCT a_id FROM o_produkt AS p, o_adresse AS a, o_kat_prod AS k
WHERE a.id = p.a_id AND p.p_id = k.p_id AND k.l4_id = $1) AS foo;'
LANGUAGE 'sql';

but trying
CREATE FUNCTION "o_l4_a_id_count" (character(30)) RETURNS integer AS...
may be a little bit faster, but miles away from the plain statement.

The l4_id are declared as char(30) in the tables. Now i know these are
max. 15 digit numbers, so is it possible to speed this funtion up by
converting them to bigint?


greetings
-tb
--
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.