Skip site navigation (1) Skip section navigation (2)

Re: Query is slow when executing in procedure

From: "ramasubramanian" <ramasubramanian(dot)g(at)renaissance-it(dot)com>
To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query is slow when executing in procedure
Date: 2009-11-24 06:29:09
Message-ID: EF820ED26A164840BFBC34D5B44559E3@ramasubramanian (view raw or flat)
Thread:
Lists: pgsql-performance
Thanks a lot Pavel . i will try it .

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


2009/11/24 ramasubramanian <ramasubramanian(dot)g(at)renaissance-it(dot)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

In response to

pgsql-performance by date

Next:From: ramasubramanianDate: 2009-11-24 06:29:48
Subject: Re: Query is slow when executing in procedure
Previous:From: A. KretschmerDate: 2009-11-24 06:15:52
Subject: Re: Query is slow when executing in procedure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group