Re: Hot Standby performance issue

Hi,

Yes, you are right. The table is the biggest one . Please find below the
information you requested. I agree the fact that autovacuum ran on this
table would fix the performance issue on standby does not sound very
convincing. But that is the only thing I could correlate when the query on
standby started working again. Otherwise there is absolutely no changes at
code level , database level or OS level.
As of now query is still working fine on standby.

I may be wrong, but could it be the case that standby disk was too much
fragmented compare to primary and autovaccum on primary fixed that.
(Assuming autovacuum on primary internally triggers the same on standby)

Sequential Scans 18
Sequential Tuples Read 1355777067
Index Scans 102566124
Index Tuples Fetched 67155748
Tuples Inserted 16579520
Tuples Updated 17144291
Tuples Deleted 24383607
Tuples HOT Updated 1214531
Live Tuples 101712125
Dead Tuples 3333207
Heap Blocks Read 420703920
Heap Blocks Hit 496135814
Index Blocks Read 66807468
Index Blocks Hit 916783267
Toast Blocks Read 310677
Toast Blocks Hit 557735
Toast Index Blocks Read 6959
Toast Index Blocks Hit 936473
Last Vacuum
Last Autovacuum 2013-10-25 02:47:09.914775-04
Last Analyze
Last Autoanalyze 2013-10-25 18:39:25.386091-04
Vacuum counter 0
Autovacuum counter 2
Analyze counter 0
Autoanalyze counter 4
Table Size 46 GB
Toast Table Size 615 MB
Indexes Size 20 GB

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5776156.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.