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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to