2016-01-23 23:00 GMT+08:00 Tom Lane <t...@sss.pgh.pa.us>: > Jinhua Luo <luajit...@gmail.com> writes: >> I have a table with 70 columns, and 6 indexes. The data flow is a >> special OLTP model: frequent inserts (2000 tps), and each inserted row >> would be updated very soon (i.e. the number of inserts is equal to the >> number of updates). > > Do those predictable updates change any of the indexed columns?
The update statement itself do not modify the indexed columns, but the before update trigger modifies two indexed columns: one is in timestamp type, used to record the update time, the trigger would fill CURRENT_TIMESTAMP into this column; the other is status, which would be set to 'done'. These two columns are indexed in btree. > >> I do a simple test: I truncate the table, disable the autovacuum, and >> run the application for a few minutes, then I invokes vacuum manually, >> it gives a strange output: >> found 598 removable, 25662 nonremovable row versions in 3476 pages >> DETAIL: 0 dead row versions cannot be removed yet >> As said before, the number of inserts is equal to the number of >> updates. So the bloat of the table should be 100%, and the number of >> removable rows should be equal to the number of nonremovable rows, >> which is the real number of inserts issued by the application. > > What seems likely is that most of the updates are HOT (because they > don't change any indexed columns) and then the freed space is reclaimable > by subsequent updates on the same page without needing a VACUUM. > > Watching the insert/update/hot-update counts in pg_stat_all_tables would > provide some evidence. testdb=# truncate test; TRUNCATE TABLE testdb=# vacuum test; testdb=# select pg_stat_reset_single_table_counters(42515); pg_stat_reset_single_table_counters ------------------------------------- (1 row) testdb=# select n_tup_ins,n_tup_upd,n_tup_hot_upd,n_tup_del,n_live_tup,n_dead_tup from pg_stat_all_tables where relid=42515; n_tup_ins | n_tup_upd | n_tup_hot_upd | n_tup_del | n_live_tup | n_dead_tup -----------+-----------+---------------+-----------+------------+------------ 0 | 0 | 0 | 0 | 0 | 0 (1 row) ##### insert 6 rows testdb=# select n_tup_ins,n_tup_upd,n_tup_hot_upd,n_tup_del,n_live_tup,n_dead_tup from pg_stat_all_tables where relid=42515; n_tup_ins | n_tup_upd | n_tup_hot_upd | n_tup_del | n_live_tup | n_dead_tup -----------+-----------+---------------+-----------+------------+------------ 6 | 6 | 0 | 0 | 6 | 6 (1 row) testdb=# vacuum verbose test; INFO: vacuuming "public.test" INFO: scanned index "test_pkey" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "deliver_done_date_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "deliver_task_queue_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "message_id_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "status_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "status_report_done_date_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "submit_done_date_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "tp_scts_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": removed 6 row versions in 1 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "test_pkey" now contains 6 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "deliver_done_date_idx" now contains 6 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "deliver_task_queue_idx" now contains 0 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "message_id_idx" now contains 6 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "status_idx" now contains 6 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "status_report_done_date_idx" now contains 6 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "submit_done_date_idx" now contains 6 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "tp_scts_idx" now contains 6 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": found 6 removable, 6 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "pg_toast.pg_toast_42515" INFO: index "pg_toast_42515_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_42515": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM ##### insert another 6 rows testdb=# select n_tup_ins,n_tup_upd,n_tup_hot_upd,n_tup_del,n_live_tup,n_dead_tup from pg_stat_all_tables where relid=42515; n_tup_ins | n_tup_upd | n_tup_hot_upd | n_tup_del | n_live_tup | n_dead_tup -----------+-----------+---------------+-----------+------------+------------ 12 | 12 | 0 | 0 | 12 | 6 (1 row) testdb=# vacuum verbose test; INFO: vacuuming "public.test" INFO: scanned index "test_pkey" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "deliver_done_date_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "deliver_task_queue_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "message_id_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "status_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "status_report_done_date_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "submit_done_date_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "tp_scts_idx" to remove 6 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": removed 6 row versions in 1 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "test_pkey" now contains 12 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "deliver_done_date_idx" now contains 12 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "deliver_task_queue_idx" now contains 0 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "message_id_idx" now contains 12 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "status_idx" now contains 12 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "status_report_done_date_idx" now contains 12 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "submit_done_date_idx" now contains 12 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "tp_scts_idx" now contains 12 row versions in 2 pages DETAIL: 6 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": found 2 removable, 12 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "pg_toast.pg_toast_42515" INFO: index "pg_toast_42515_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_42515": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM You could see that I insert 6 rows two times. The first vacuum works, which found 6 rows to remove. The second vacuum is wrong, which only found 2 rows to remove. However, for indexes, both found 6 rows to remove. No HOT updates. So is there any other reasons? Regards, Jinhua Luo -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers