Hi Tomas, Thanks so much for your response and sorry for not providing the enough details. I have attached the zip file which has query,explain plan and database parameter settings for both primary and secondary. Please note that query has multiple unions only the first query on top is causing the performance issue. Transaction search is one of the feature in our Admin user interface(web portal) where user can search for the transactions against our OLTP database. The attached query is generated dynamically by the application. > (3) The load on standby does not seem to be issue, because with > absolutely no load the query takes long and most of the time > returned with the conflict error. Not suse I understand this. Are you saying that the standby is mostly idle, i.e. the query seems to be stuck, and then fails with conflict error most of the time? The standby is not idle all the time. What I meant was even with no user activity or no active user sessions, if I issue the query directly from pgadmin tool it takes for ever. Hardware settings both primary and secondary : =================================== Red Hat Enterprise Linux Server release 5.5 (Tikanga) Linux 2.6.18-194.26.1.el5 x86_64 4 CPUs 16 GB RAM Intel Xeon Postgresql Version: ================= "PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit" 6) After we recovered standby it was fine for few weeks and then > again started slowing down. Was it slowing down gradually, or did it start failing suddenly? Honestly speaking I do not exactly, when users started reporting the issue I started looking into it. But the performance was good in September and somewhere in October it started slowing down. I guess it was gradual. There were no code change in the application or major change in the data volume. Hope this helps. Please let me know if you need any other details. Thanks Again. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775123.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
