Thread: 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.