Re: 8.2 Autovacuum BUG ?

Hi all,

Regarding this archive log generation found one observation.  

A table named abc_table id found to be archived every 9'th and 39'th minute.
We are able to find number of tuples deleted from the pg_stat_user_tables
view. 

But to my wonder the number of tuple inserts are shown 0.  How can there be
any delete without any inserts.

It was found that the table is having 2060 rows, where in which all rows are
getting deleted in every 9'th and 39'th minute of an hour. It implies that
those deleted should be inserted before the delete operation.

Also performed vacuum freeze on that table before 9'th minute of an hour it
generated 36 archive logs, and when I tried to do the same operation after
9'th minute(say 11'th minute of the same hour), it is generating the same
number of archive logs.

This is possible only if  the entire table gets updated/recreated.  Now my
final doubt is why the tuple inserts in pg_stat_user_tables is showing 0,
when corresponding deletes are existing?

Please find the below outputs FYR.


--Steps performed on production server:--

--1. Found Count Of Rows in Production
--******************************************
prod_erp=# select count(*) from abc_table;;
 count
-------
  2060
(1 row)

--2. Issued 'Select pg_stat_reset();'

--3. Before Delete Statements (Before JAN 31'st 2018 14:09 Hrs)
--****************************************************************

Issued:

select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-----+----------------------------
relid             | 550314
schemaname        | public
relname           | abc_table
seq_scan          | 2
seq_tup_read      | 4120
idx_scan          | 0
idx_tup_fetch     | 0
n_tup_ins         | 0
n_tup_upd         | 0
n_tup_del         | 0
n_tup_hot_upd     | 0
n_live_tup        | 0
n_dead_tup        | 0
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 0


--4. After Delete Statements (Before JAN 31'st 2018 14:09 Hrs)
--****************************************************************

select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-----+----------------------------
relid             | 550314
schemaname        | public
relname           | abc_table
seq_scan          | 3
seq_tup_read      | 6180
idx_scan          | 2060
idx_tup_fetch     | 2060
n_tup_ins         | 0
n_tup_upd         | 0
n_tup_del         | 2060
n_tup_hot_upd     | 0
n_live_tup        | 0
n_dead_tup        | 0
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 0


--5. After Delete Statements (Before JAN 31'st 2018 14:39 Hrs)
--****************************************************************  

select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-----+----------------------------
relid             | 550314
schemaname        | public
relname           | abc_table
seq_scan          | 4
seq_tup_read      | 8240
idx_scan          | 4120
idx_tup_fetch     | 4120
n_tup_ins         | 0
n_tup_upd         | 0
n_tup_del         | 4120
n_tup_hot_upd     | 0
n_live_tup        | 0
n_dead_tup        | 0
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 0


--6. After Delete Statements (Before JAN 31'st 2018 15:09 Hrs)
--****************************************************************  


select * from pg_stat_user_tables where relname ='abc_table';
-[ RECORD 1 ]-----+----------------------------
relid             | 550314
schemaname        | public
relname           | abc_table
seq_scan          | 5
seq_tup_read      | 10300
idx_scan          | 6180
idx_tup_fetch     | 6180
n_tup_ins         | 0
n_tup_upd         | 0
n_tup_del         | 6180
n_tup_hot_upd     | 0
n_live_tup        | 0
n_dead_tup        | 0
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 0



As said above if we compare n_tup_del value in steps 4,5,6 it says us that
entire table is getting deleted(correct me if I'm wrong), but n_tup_ins is
0. 

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html