Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

It seems that we are currently running 8.4.3 on the server we are encountering the problem. Will upgrade to 8.4.9 and then will come back with a test case if we still see the issue Thanks again for your help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Statistics-

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

tim_wilson writes: > When you say recent releases, does that include 8.4? 8.4.9 and later --- anything including this commit: Author: Tom Lane Branch: master Release: REL9_1_BR [b4b6923e0] 2011-05-30 17:06:52 -0400 Branch: REL9_0_STABLE Release: REL9_0_5 [73bd34c81] 2011-05-30 17:07:07 -0400 B

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

Thanks for the reply Tom, will try and construct test case. Have been unable to replicate in a simple test the sort of updates that the table out in the wild is seeing, so may impact that issue of vacuum finding unrepresentative sample, maybe. Will try harder! When you say recent releases, does

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

tim_wilson writes: > This drift gets more confusing. In recent releases, reltuples (and relpages) are updated via a "moving average" calculation that is meant to converge on the true value over time. The reason vacuum has to act that way is that it typically doesn't scan the whole table anymore

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

On 12/06/2012 06:45 PM, tim_wilson wrote: 86% of the updates are HOT updates! The difference between the first and second line of this image above is that 366 updates happened of which 299 where HOT. And a vacuum on the t

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

86% of the updates are HOT updates! The difference between the first and second line of this image above is that 366 updates happened of which 299 where HOT. And a vacuum on the table was run. Look at the retuples number c

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

On 12/06/2012 06:13 PM, tim_wilson wrote: This drift gets more confusing. My small table A (60K rows) is not being inserted to (except one or two rows) it is getting thousands of updates a minute. Analyze and vacuum on the table are running regularly. But sometimes ,every time the vacuum runs th

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

I am also seeing a drift in the n_live_tup value compared to actual row count on the table on PG9.0.6 It drifts after a vacuum , you can bring it back closer to the actual number by running ANALYSE several times, you can lock it back into the right value with a vacuum full, but then if you run a v

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

> Andreas Brandl writes: > >> The planner doesn't use n_live_tup; > > > I'm just curious: where does the planner take the (approximate) > > row-count from? > > It uses the tuple density estimated by the last vacuum or analyze > (viz, > reltuples/relpages) and multiplies that by the current relat

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

Andreas Brandl writes: >> The planner doesn't use n_live_tup; > I'm just curious: where does the planner take the (approximate) row-count > from? It uses the tuple density estimated by the last vacuum or analyze (viz, reltuples/relpages) and multiplies that by the current relation size. There a

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

Hi, > Andreas Brandl writes: > > we're currently investigating a statistics issue on postgres. We > > have some tables which frequently show up with strange values for > > n_live_tup. If you compare those values with a count on that > > particular table, there is a mismatch of factor 10-30. This

Re: [GENERAL] Statistics mismatch between n_live_tup and actual row count

Andreas Brandl writes: > we're currently investigating a statistics issue on postgres. We have some > tables which frequently show up with strange values for n_live_tup. If you > compare those values with a count on that particular table, there is a > mismatch of factor 10-30. This causes the p