[HACKERS] VACUUM and ANALYZE disagreeing on what reltuples means

Hi,

It seems to me that VACUUM and ANALYZE somewhat disagree on what exactly 
reltuples means. VACUUM seems to be thinking that
    reltuples = live + dead

while ANALYZE apparently believes that
    reltuples = live

This causes somewhat bizarre changes in the value, depending on which of 
those commands was executed last.

To demonstrate the issue, let's create a simple table with 1M rows, 
delete 10% rows and then we'll do a bunch of VACUUM / ANALYZE and check 
reltuples, n_live_tup and n_dead_tup in the catalogs.

I've disabled autovacuum so that it won't interfere with this, and 
there's another transaction blocking VACUUM from actually cleaning any 
dead tuples.

    test=# create table t as           select i from generate_series(1,1000000) s(i);
    test=# select reltuples, n_live_tup, n_dead_tup             from pg_stat_user_tables join pg_class using (relname)
         where relname = 't';
 
     reltuples | n_live_tup | n_dead_tup    -----------+------------+------------         1e+06 |    1000000 |
0

So, that's nice. Now let's delete 10% of rows, and run VACUUM and 
ANALYZE a few times.
    test=# delete from t where random() < 0.1;
    test=# vacuum t;
    test=# select reltuples, n_live_tup, n_dead_tup             from pg_stat_user_tables join pg_class using (relname)
         where relname = 't';
 
     reltuples | n_live_tup | n_dead_tup    -----------+------------+------------         1e+06 |     900413 |
99587

    test=# analyze t;
     reltuples | n_live_tup | n_dead_tup    -----------+------------+------------        900413 |     900413 |
99587
    test=# vacuum t;
     reltuples | n_live_tup | n_dead_tup    -----------+------------+------------         1e+06 |     900413 |
99587


So, analyze and vacuum disagree.

To further confuse the poor DBA, VACUUM always simply ignores the old 
values while ANALYZE combines the old and new values on large tables 
(and converges to the "correct" value after a few steps). This table is 
small (less than 30k pages), so ANALYZE does not do that.

This is quite annoying, because people tend to look at reltuples while 
investigating bloat (e.g. because the check_postgres query mentioned on 
our wiki [1] uses reltuples in the formula).

[1] https://wiki.postgresql.org/wiki/Show_database_bloat

And when the cleanup is blocked for some reason (as in the example 
above), VACUUM tends to be running much more often (because it can't 
cleanup anything). So reltuples tend to be set to the higher value, 
which I'd argue is the wrong value for estimating bloat.

I haven't looked at the code yet, but I've confirmed this happens both 
on 9.6 and 10. I haven't checked older versions, but I guess those are 
affected too.

The question is - which of the reltuples definitions is the right one? 
I've always assumed that "reltuples = live + dead" but perhaps not?

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services