Re: Statistics mismatch between n_live_tup and actual row count

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: tim_wilson <tim(dot)wilson(at)telogis(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Statistics mismatch between n_live_tup and actual row count
Date: 2012-12-07 03:19:22
Message-ID: 10296.1354850362@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

tim_wilson <tim(dot)wilson(at)telogis(dot)com> 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, but only the pages that have been
dirtied recently. So it measures the tuple density in the pages it
scans, and updates the previous value more or less aggressively
depending on the percentage of the pages that it looked at.

It's possible that there's some bug in that algorithm, but what seems
more likely is that the pages that are getting dirtied are
nonrepresentative of the whole table for some reason. Or maybe both.

Can you put together a self-contained test case showing this behavior?

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message tim_wilson 2012-12-07 03:29:13 Re: Statistics mismatch between n_live_tup and actual row count
Previous Message ac@hsk.hk 2012-12-07 03:17:27 Ubutu 12.04 and PostgreSQL9.2.1