-- 20171024. Reproducing vacuum issue in VM environment
-- Pavel Suderevsky (psuderevsky@gmail.com)
-- PostgreSQL 9.6.5 (standard installation and with vacuum-reltuples-fix-v2.patch applied)
-- CentOS Linux release 7.2.1511

-- SOME INVOLVED PG SETTINGS
autovacuum | on
autovacuum_naptime | 10s
autovacuum_vacuum_scale_factor | 0.002
autovacuum_vacuum_threshold | 100
autovacuum_analyze_scale_factor | 0.0009
autovacuum_analyze_threshold | 50
autovacuum_vacuum_cost_delay | 12ms
autovacuum_vacuum_cost_limit | 1200
default_statistics_target | 100


------------------------------------------------------------------------------------------
I.
DEFAULT POSTGRESQL 9.6.5 INSTALLATION, PATCH NOT APPLIED
https://www.postgresql.org/message-id/94f58897-861b-accf-a9f1-af4be816c0d3@2ndquadrant.com
------------------------------------------------------------------------------------------

-- REAL NUMBER OF ROWS
database=# select count(*) from table1;
 count 
-------
    26
(1 row)

-- GOOD STATISTICS WITH ANALYZE
database=# analyze table1;
ANALYZE
database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 12:07:03.391809+00 | table1 |             26     |         26 |          0 |               78 | 2017-10-24 12:02:27.299773+00 | 2017-10-24 11:55:07.030929+00
(1 row)

-- OPEN TRANSACTION IN SEPARATE SESSION IN ORDER TO PREVENT TABLE FROM BEING AUTOVACUUMED
-- PERFORM A NUMBER OF UPDATES IN TABLE (18 of 26 rows updated 11 times)
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18
database=# update table1 set version = version + 1 where id > 250;
UPDATE 18

-- AV STARTS TO PERFORM EVERY 10 SECONDS (as naptime setting set).

database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze       
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+------------------------------
 2017-10-24 12:08:04.180194+00 | table1 |             26     |         26 |        216 |               79 | 2017-10-24 12:08:00.171796+00 | 2017-10-24 12:08:00.17294+00
(1 row)
-- AFTER THE VERY FIRST AV reltuples VALUE EQUALS n_live_tup + n_dead_tup
database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |       last_autovacuum        |       last_autoanalyze       
-------------------------------+----------+--------------------+------------+------------+------------------+------------------------------+------------------------------
 2017-10-24 12:08:12.721282+00 | table1 |            242     |         44 |        198 |               80 | 2017-10-24 12:08:10.16969+00 | 2017-10-24 12:08:00.17294+00
(1 row)

-- EVERY TIME AV PERFORMS reltuples VALUE IS DRAMATICALLY INCREASED

database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze       
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+------------------------------
 2017-10-24 12:08:20.720084+00 | table1 |            444     |        246 |        198 |               81 | 2017-10-24 12:08:20.174245+00 | 2017-10-24 12:08:00.17294+00
(1 row)

database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze       
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+------------------------------
 2017-10-24 12:08:34.202002+00 | table1 |            632     |        434 |        198 |               82 | 2017-10-24 12:08:30.176914+00 | 2017-10-24 12:08:00.17294+00
(1 row)

database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze       
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+------------------------------
 2017-10-24 12:08:41.692696+00 | table1 |            807     |        609 |        198 |               83 | 2017-10-24 12:08:40.187175+00 | 2017-10-24 12:08:00.17294+00
(1 row)

-- AGAIN REAL ROWS COUNT
database=# select count(*) from table1;
 count 
-------
    26
(1 row)

-- AS A RESULT ALL DEAD TUPLES REMOVED BUT STATISTIC WILL BE IN INCONSISTENT STATE UNTIL NEXT ANALYZE: 1124 INSTEAD OF 26 ROWS ESTIMATED IN EVEN LESS THAN A MINUTE AFTER UPDATES
database=# EXPLAIN select count(*) from table1;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Aggregate  (cost=82.99..83.00 rows=1 width=8)
   ->  Index Only Scan using account_id on table1  (cost=0.28..80.18 rows=1124 width=0)
(2 rows)





------------------------------------------------------------------------------------------
II.
RECOMPILED WITH PATCH
https://www.postgresql.org/message-id/94f58897-861b-accf-a9f1-af4be816c0d3@2ndquadrant.com
------------------------------------------------------------------------------------------

-- REAL NUMBER OF ROWS
database=# select count(*) from table1;
 count 
-------
    26
(1 row)

-- GOOD STATISTICS WITH ANALYZE
database=# vacuum analyze table1;                    
VACUUM
database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:52:59.886284+00 | table1 |             26     |         26 |          0 |               33 | 2017-10-24 11:52:06.940907+00 | 2017-10-24 11:46:46.826939+00
(1 row)

-- BAD STATISTICS WITH VACUUM
database=# vacuum table1;
VACUUM
database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:53:08.484687+00 | table1 |             32     |         32 |          0 |               33 | 2017-10-24 11:52:06.940907+00 | 2017-10-24 11:46:46.826939+00
(1 row)

-- OPEN TRANSACTION IN SEPARATE SESSION IN ORDER TO PREVENT TABLE FROM BEING AUTOVACUUMED
-- PERFORM A NUMBER OF UPDATES IN TABLE (18 of 26 rows updated 10 times)
-- AV STARTS TO PERFORM EVERY 10 SECONDS (as naptime setting set).
-- EVERY TIME AV PERFORMS reltuples VALUE IS INCREASED
database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:55:33.413182+00 | table1 |             60     |         60 |        180 |               36 | 2017-10-24 11:55:27.032327+00 | 2017-10-24 11:55:07.030929+00
(1 row)

database=# \watch 10
                                                                  Watch every 10s       Tue Oct 24 11:56:10 2017

              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:56:10.016716+00 | table1 |            112     |        112 |        180 |               40 | 2017-10-24 11:56:07.055565+00 | 2017-10-24 11:55:07.030929+00
(1 row)

                                                                 Watch every 10s        Tue Oct 24 11:56:20 2017

             now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:56:20.04525+00 | table1 |            122     |        122 |        180 |               41 | 2017-10-24 11:56:17.055772+00 | 2017-10-24 11:55:07.030929+00
(1 row)

                                                                  Watch every 10s       Tue Oct 24 11:56:30 2017

              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:56:30.078489+00 | table1 |            131     |        131 |        180 |               42 | 2017-10-24 11:56:27.064416+00 | 2017-10-24 11:55:07.030929+00
(1 row)

                                                                  Watch every 10s       Tue Oct 24 11:56:40 2017

              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:56:40.104698+00 | table1 |            140     |        140 |        180 |               43 | 2017-10-24 11:56:37.065533+00 | 2017-10-24 11:55:07.030929+00
(1 row)

                                                                  Watch every 10s       Tue Oct 24 11:56:50 2017

              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:56:50.132043+00 | table1 |            148     |        148 |        180 |               44 | 2017-10-24 11:56:47.066793+00 | 2017-10-24 11:55:07.030929+00
(1 row)

                                                                 Watch every 10s        Tue Oct 24 11:57:00 2017

             now             | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-----------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:57:00.1587+00 | table1 |            155     |        155 |        180 |               45 | 2017-10-24 11:56:57.072697+00 | 2017-10-24 11:55:07.030929+00
(1 row)

-- QUERY OPTIMIZER TAKES WRONG reltuples VALUE FOR ROWS ESTIMATION 
database=# EXPLAIN SELECT * FROM table1;
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on table1  (cost=0.00..105.62 rows=162 width=173)
(1 row)

database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 11:59:24.640336+00 | table1 |            162     |        162 |        180 |               59 | 2017-10-24 11:59:17.125873+00 | 2017-10-24 11:55:07.030929+00
(1 row)

-- STOP TRANSACTION IN SESSION #2 THAT PREVENTS AV PROCESSING

database=#  select NOW(), pg_class.relname, to_char(pg_class.reltuples, '9G999G999G999') as pg_class_reltuples, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum, last_autoanalyze from pg_class, pg_stat_user_tables where pg_class.relname = pg_stat_user_tables.relname and pg_class.relname = 'table1';
              now              | relname  | pg_class_reltuples | n_live_tup | n_dead_tup | autovacuum_count |        last_autovacuum        |       last_autoanalyze        
-------------------------------+----------+--------------------+------------+------------+------------------+-------------------------------+-------------------------------
 2017-10-24 12:02:40.674332+00 | table1 |            218     |        218 |          0 |               78 | 2017-10-24 12:02:27.299773+00 | 2017-10-24 11:55:07.030929+00
(1 row)

-- AS A RESULT ALL DEAD TUPLES REMOVED BUT STATISTIC WILL BE IN INCONSISTENT STATE UNTIL NEXT ANALYZE

database=# EXPLAIN SELECT * FROM table1;
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on table1  (cost=0.00..106.18 rows=218 width=173)
(1 row)





Conclusions: 
1. Patch fixed issue with adding n_dead_tup value to reltuples.
2. Even without dead_tuples in a table, vacuum without analyze would bring reltuples value to inconsistent state.
3. reltuples value increases with absolutely every lazy vacuum iteration.
4. Existence of dead tuples would just increase inaccuracy of reltuples because of higher vacuum operations rate (depends on autovacuum_naptime value) and higher iteration increment of reltuples overvaluing.
5. Bug is pretty critical, especially with big tables with high modification rate.