Re: Mismatched pg_class.reltuples between table and primary key

bricklen <bricklen(at)gmail(dot)com> writes:
> We recently experienced a hard crash of a dev server due to lack of
> resources (we think, still investigating).
> That resulted in an interesting scenario where one of the tables was
> returning 12 rows from "select * from tbl", but "select * from tbl order by
> <pk column>" was only returning 11.
> Looking at pg_class.reltuples, it could be clearly seen that the table in
> question had a value of 12, but the primary key had only 11.

> My system catalog ignorance is probably showing, but I assumed that the
> table and PK pg_class.reltuples values should always be the same?

I wouldn't assume that --- they're only approximations, anyway.
In particular there's some moving-average behavior in there that might
prevent small errors in the value from ever going away completely.

> The initial observations were made by a developer:
> 1). After an ANALYZE, PK reltuples remained at 11.
> 2). After REINDEX, PK reltuples was still 11.
> 3). After VACUUM FULL, PK reltuples was reset to 12.

(2) is a bit surprising, but I'm not sure that REINDEX recomputes the
reltuples value for the index.

regards, tom lane