Postgresql: The magical way of looking at row counts on a table in postgresql.

On volatile table, the row counts can be off so much between vacum. I have seen a case where the table simply not reflecting the real rows counts for weeks. 

Looking at pg_class. The vpx_event_arg table have had that counts for awhile. The reading from pg_class and pg_stat_all_tables are off too. I am not sure what is the right row counts for the table anymore.

select relname as TABLE_NAME,reltuples AS ROW_NUMBERS FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') and relname ='vpx_event_arg' AND relkind='r'
ORDER BY reltuples DESC;

  table_name   | row_numbers

---------------+-------------
vpx_event_arg |      215304
(1 row)


VCDB=# select relname, reltuples from pg_class where relname ='vpx_event_arg';

    relname    | reltuples
---------------+-----------
vpx_event_arg |    215304
(1 row)

Taking the insert and minus the delete in hoping to find the stale data. 

VCDB=#  SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables where relname = 'vpx_event_arg';

    relname    | rowcount
---------------+----------
vpx_event_arg |     7569
(1 row)



VCDB=# SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables where relname = 'vpx_event_arg';
schemaname |    relname    | n_live_tup
------------+---------------+------------
vc         | vpx_event_arg |     214571
(1 row)

VCDB=#  select 214571 - 215304;
?column?
----------
     -733
(1 row)



http://www.postgresql.org/docs/9.3/static/catalog-pg-class.html

The document states that the reltuples will be updated after VACUUM and ANALYZE

VCDB=# analyze vpx_event_arg;
ANALYZE


select relname as TABLE_NAME,reltuples AS ROW_NUMBERS FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') and relname ='vpx_event_arg' AND relkind='r'
ORDER BY reltuples DESC;


VCDB=# select relname, reltuples from pg_class where relname ='vpx_event_arg';
    relname    | reltuples
---------------+-----------
vpx_event_arg |    221059
(1 row)

VCDB=# SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables where relname = 'vpx_event_arg';
    relname    | rowcount
---------------+----------
vpx_event_arg |     7569
(1 row)

VCDB=# SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables where relname = 'vpx_event_arg';
schemaname |    relname    | n_live_tup
------------+---------------+------------
vc         | vpx_event_arg |     221059
(1 row)

VCDB=# select 221059 - 221059;
?column?
----------
        0
(1 row)

VCDB=#

As  you can see the table has 733 off between pg_class and pg_stat_all_tables. The  n_tup_ins minus n_tup_del (7569)supposing-ly a pretty reliable reading but far off in my case. I am not sure which number I should trust here. 

However, I still recommend that, if a DBA would like to find out a fairly accurate table count, do an Analyze first.