Re: questions about a table's row estimates

Greg Smith <greg(at)2ndquadrant(dot)com> writes:
> Ben Chobot wrote:
>> I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about n_live_tup. Shouldn't that be at least fairly close to (n_tup_ins - n_tup-del)? It doesn't seem to be, but I'm unclear why.
>>
> Insert 2000 tuples.
> Delete 1000 tuples.
> vacuum
> Insert 1000 tuples. These go into the free space the deleted tuples used
> to be in.
> analyze

> n_tup_ins=3000
> n_tup_del=1000
> n_live_tup=3000

Huh?

regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo select generate_series(1,2000);
INSERT 0 2000
regression=# select n_live_tup,n_tup_ins,n_tup_del from pg_stat_user_tables where relname = 'foo';
n_live_tup | n_tup_ins | n_tup_del
------------+-----------+-----------
2000 | 2000 | 0
(1 row)

regression=# delete from foo where f1 > 1000;
DELETE 1000
regression=# select n_live_tup,n_tup_ins,n_tup_del from pg_stat_user_tables where relname = 'foo';
n_live_tup | n_tup_ins | n_tup_del
------------+-----------+-----------
1000 | 2000 | 1000
(1 row)

regression=# insert into foo select generate_series(2001,3000);
INSERT 0 1000
regression=# select n_live_tup,n_tup_ins,n_tup_del from pg_stat_user_tables where relname = 'foo';
n_live_tup | n_tup_ins | n_tup_del
------------+-----------+-----------
2000 | 3000 | 1000
(1 row)

regression=#

The only easy explanation I can think of for Ben's complaint is if he
reset the stats counters sometime during the table's existence.

regards, tom lane