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;
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';
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.
---------------+-----------
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
---------------+----------
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.
No comments:
Post a Comment