Обсуждение: Fast statement but slow function

Hallo,

i'm new to the the list but using postgres since the 6.x days.

Now i run into a problem creating a function instead of executing
the same statements many times. But there is a _very__big_
performance difference between the results.

The following statement executes very fast (less than a half
of a second) on my installation:

SELECT count(a_id) FROM (
  SELECT DISTINCT a_id FROM o_kat_ausst AS k
    WHERE k.l4_id = '140000000000007'
  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 = '140000000000007'
) AS foo;

but when i create the following function
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';

and do the statement
SELECT o_l4_a_id_count('140000000000007');
it takes more than 4 seconds.

Is this a bug in my function or in my mind or in postgres?
Are functions using indexes?
This problem happens on versionis 7.1.2 and 7.2.1 (i just have
to change the return value for the function to bigint instead of int).

I checked all indices on involved tables but can't find a mistake.
BTW: I'm using the same functions with the l1_id, l2_id, l3_id instead
of l4_id, and these functions looks as fast as the real statements.

Thanks for reading and help!

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

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.

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.

            regards, tom lane

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.