0 like 0 dislike
107 views
asked by (2.4k points)  
edited by

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (2k points)  
 
Best answer

If table having large number of dead tuble you have to vacuum or analyze the table

For Example:

STEP 1. update the k1 table again for making operation of  VACUUM (VERBOSE, ANALYZE)

update k1 set tableowner='john';

STEP 2.Check the dead tubles

postgres=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='k1';
 n_dead_tup |          last_vacuum          | last_analyze | n_tup_upd | n_tup_del | n_tup_hot_upd | relname | seq_scan | idx_scan 
------------+-------------------------------+--------------+-----------+-----------+---------------+---------+----------+----------
    1880064 | 2017-05-15 05:20:05.216778-04 |              |   4708352 |      8192 |          8333 | k1      |       21 |         
(1 row)

STEP 3.Now let us making VACUUM

VACUUM (VERBOSE, ANALYZE);

STEP 4.Again check the dead tubles

postgres=# select n_dead_tup ,last_vacuum,last_analyze,n_tup_upd, n_tup_del,n_tup_hot_upd,relname ,seq_scan,idx_scan  from pg_stat_all_tables where relname='k1';
 n_dead_tup |          last_vacuum          |         last_analyze          | n_tup_upd | n_tup_del | n_tup_hot_upd | relname | seq_scan | idx_scan 
------------+-------------------------------+-------------------------------+-----------+-----------+---------------+---------+----------+----------
          0 | 2017-05-15 13:48:05.985591-04 | 2017-05-15 13:48:16.555511-04 |   4708352 |      8192 |          8333 | k1      |       21 |         
(1 row)

For more details: Vacuum and analyze

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
...