| Lists: | pgsql-performance |
|---|
| From: | "Anoo Sivadasan Pillai" <aspillai(at)in(dot)rm(dot)com> |
|---|---|
| To: | <pgsql-performance(at)postgresql(dot)org> |
| Subject: | After Vacuum Analyse - Procedure performance not improved - Innner select is faster |
| Date: | 2008-01-09 04:03:57 |
| Message-ID: | 772CDA49C515244FA14E34443F62E56403AFC90C@EX-INDIA1.internal.rmplc.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
Hi All,
I have a procedure which contains only one Select statement. The
procedure take more time than expected to complete, the select statement
inside the procedure was taking about 2 minutes to complete.
After running a Vacuum Analyze on the underlying tables the select
statement could complete within seconds. But the procedure was still
taking long time.
I tried after recreating the procedure, the procedure is taking only a
few seconds after recreation.
Why the procedure is not getting the performance advantage of Vacuum
analyse?
Regards,
Anoo
Visit our Website at http://www.rmesi.co.in
This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.
Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.
This email has been scanned for viruses by Trend ScanMail.
| From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
|---|---|
| To: | Anoo Sivadasan Pillai <aspillai(at)in(dot)rm(dot)com> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: After Vacuum Analyse - Procedure performance not improved - Innner select is faster |
| Date: | 2008-01-09 05:06:44 |
| Message-ID: | 20080109050644.GF20913@alvh.no-ip.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
Anoo Sivadasan Pillai wrote:
> Why the procedure is not getting the performance advantage of Vacuum
> analyse?
Plan caching by the function, probably. Try disconnecting the session
and reconnecting to prove the hypothesis.
If it is a recurring problem for you, you could put the SELECT under
EXECUTE in the function. But most likely this is just a one-time
problem.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
| From: | "Anoo Sivadasan Pillai" <aspillai(at)in(dot)rm(dot)com> |
|---|---|
| To: | "Alvaro Herrera" <alvherre(at)commandprompt(dot)com> |
| Cc: | <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: After Vacuum Analyse - Procedure performance notimproved - Innner select is faster |
| Date: | 2008-01-09 06:00:33 |
| Message-ID: | 772CDA49C515244FA14E34443F62E56403AFC9B8@EX-INDIA1.internal.rmplc.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
>> Why the procedure is not getting the performance advantage of Vacuum
>> analyse?
>Plan caching by the function, probably. Try disconnecting the session
>and reconnecting to prove the hypothesis.
>If it is a recurring problem for you, you could put the SELECT under
>EXECUTE in the function. But most likely this is just a one-time
>problem.
Is there any way to clear the cached plan manually other than
disconnecting (With the help of some commands/Configuration settings) ?
Visit our Website at http://www.rmesi.co.in
This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.
Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.
This email has been scanned for viruses by Trend ScanMail.
| From: | Erik Jones <erik(at)myemma(dot)com> |
|---|---|
| To: | Anoo Sivadasan Pillai <aspillai(at)in(dot)rm(dot)com> |
| Cc: | "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: After Vacuum Analyse - Procedure performance notimproved - Innner select is faster |
| Date: | 2008-01-09 18:06:35 |
| Message-ID: | EB93DCC6-217E-4CE9-B673-2FE2B03B6BBD@myemma.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
On Jan 9, 2008, at 12:00 AM, Anoo Sivadasan Pillai wrote:
>
>>> Why the procedure is not getting the performance advantage of Vacuum
>>> analyse?
>
>> Plan caching by the function, probably. Try disconnecting the
>> session
>> and reconnecting to prove the hypothesis.
>
>> If it is a recurring problem for you, you could put the SELECT under
>> EXECUTE in the function. But most likely this is just a one-time
>> problem.
>
> Is there any way to clear the cached plan manually other than
> disconnecting (With the help of some commands/Configuration
> settings) ?
Only as of 8.3.
Erik Jones
DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com