Thread: Query is slow when executing in procedure

Query is slow when executing in procedure

From
"ramasubramanian"
Date:
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?
 
Thanks in Advance
Ram

Re: Query is slow when executing in procedure

From
Pavel Stehule
Date:
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

Re: Query is slow when executing in procedure

From
"A. Kretschmer"
Date:
In response to ramasubramanian :
> 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?

The reason is hard to guess, because you don't provide enough
informations like the function code.

My guess:

You calls the function with a parameter, and the planner isn't able to
chose a fast plan because he doesn't know the parameter. That's why he
is choosen a seq-scan. You can rewrite your function to using dynamical
execute a string that contains your sql to force the planner search an
optimal plan for your actual parameter.

But yes, that's only a wild guess (and sorry about my english...)

Please, show us the table and the function-code.


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Re: Query is slow when executing in procedure

From
"ramasubramanian"
Date:
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



Re: Query is slow when executing in procedure

From
"ramasubramanian"
Date:
Thanks a lot Kretschmer. i will try it .

Regards,
Ram

----- Original Message -----
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: <pgsql-performance@postgresql.org>
Sent: Tuesday, November 24, 2009 11:45 AM
Subject: Re: [PERFORM] Query is slow when executing in procedure


> In response to ramasubramanian :
>> 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?
>
> The reason is hard to guess, because you don't provide enough
> informations like the function code.
>
> My guess:
>
> You calls the function with a parameter, and the planner isn't able to
> chose a fast plan because he doesn't know the parameter. That's why he
> is choosen a seq-scan. You can rewrite your function to using dynamical
> execute a string that contains your sql to force the planner search an
> optimal plan for your actual parameter.
>
> But yes, that's only a wild guess (and sorry about my english...)
>
> Please, show us the table and the function-code.
>
>
> Regards, Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>