X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 927609DC833
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Feb 2006 01:46:08 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 28624-04
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Feb 2006 01:46:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id 658449DC805
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Feb 2006 01:46:06 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k1D5k2mY054748
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Sun, 12 Feb 2006 22:46:04 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k1D5k1h0019216;
	Sun, 12 Feb 2006 22:46:01 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k1D5k1WT019215;
	Sun, 12 Feb 2006 22:46:01 -0700 (MST) (envelope-from mfuhr)
Date: Sun, 12 Feb 2006 22:46:01 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Adnan DURSUN <a_dursun@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SQL Function Performance
Message-ID: <20060213054601.GA19126@winnie.fuhr.org>
References: <BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.121 required=5 tests=[AWL=0.121]
X-Spam-Score: 0.121
X-Spam-Level: 
X-Archive-Number: 200602/140
X-Sequence-Number: 17126

On Sun, Feb 12, 2006 at 10:25:28PM +0200, Adnan DURSUN wrote:
> My database has an SQL function. The result comes in 30-40 seconds
> when i use the SQL function. On the other hand; The result comes
> 300-400 milliseconds when i run the SQL statement. Any idea ??

Have you analyzed the tables?  If that's not the problem then could
you post the EXPLAIN ANALYZE output for the direct query and for a
prepared query?  For the prepared query do this:

PREPARE stmt (varchar, date, int4, varchar, varchar) AS SELECT ... ;

where "..." is the same SQL as in the function body, including the
numbered parameters ($1, $2, etc.).  To execute the query do this:

EXPLAIN ANALYZE EXECUTE stmt (...);

Where "..." is the same parameter list you'd pass to the function
(the same values you used in the direct query).

If you need to re-prepare the query then run "DEALLOCATE stmt"
before doing so.

-- 
Michael Fuhr