Re: Query is slow when executing in procedure

Thanks a lot Pavel . i will try it .

----- Original Message -----
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "ramasubramanian" <ramasubramanian.g@renaissance-it.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Tuesday, November 24, 2009 11:40 AM
Subject: Re: [PERFORM] Query is slow when executing in procedure


2009/11/24 ramasubramanian <ramasubramanian.g@renaissance-it.com>:
> Dear all,
> The query is slow when executing in the stored procedure(it is taking
> around 1 minute). when executing as a sql it is taking 4 seconds.
> basically i am selecting the varchar column which contain 4000 character.
> We
> have as iindex on the table. We have analyzed the table also. What could
> be
> the reason. How to improve it?

Hello

use a dynamic query - plpgsql uses prepared statements. It use plans
generated without knowledge of real params. Sometime it should to do
performance problem. EXECUTE statement (in plpgsql) uses new plan for
every call (and generated with knowledge of real params) - so it is a
solution for you.

http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards
Pavel Stehule



>
> Thanks in Advance
> Ram

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance