From: Michael Fuhr <mike@fuhr.org>
To: Adnan DURSUN <a_dursun@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SQL Function Performance
Date: Sun, 12 Feb 2006 22:46:01 -0700
Message-ID: <20060213054601.GA19126@winnie.fuhr.org> (raw)
In-Reply-To: <BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl>
References: <BAY106-DAV73D3202A09EC68044469AFA040@phx.gbl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, a_dursun@hotmail.com
Subject: Re: SQL Function Performance
In-Reply-To: <20060213054601.GA19126@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox