Thread: Query is slow when executing in procedure
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
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
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
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
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 >