I have a table that has only a few records in it at the time, and they get deleted every few seconds and new records are inserted. Table never has more than 5-10 records in it. However, I noticed a deteriorating performance in deletes and inserts on it. So I performed vacuum analyze on it three times (twice in a row, and once two days later). In the statistics it says that the table size is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, even though the table has only 5-10 rows in it it. I was wondering how can I reclaim all this space and improve the performance? Here are the outputs of my vacuum sessions: ----------------------02/24/06 4:30PM---------------------- INFO: vacuuming "incidents.php_sessions" INFO: index "php_sessions_pkey" now contains 16 row versions in 17151 pages DETAIL: 878643 index row versions were removed. 16967 index pages have been deleted, 8597 are currently reusable. CPU 3.35s/3.67u sec elapsed 25.96 sec. INFO: "php_sessions": removed 878689 row versions in 107418 pages DETAIL: CPU 17.53s/11.23u sec elapsed 88.22 sec. INFO: "php_sessions": found 878689 removable, 14 nonremovable row versions in 110472 pages DETAIL: 10 dead row versions cannot be removed yet. There were 87817 unused item pointers. 0 pages are entirely empty. CPU 23.87s/16.57u sec elapsed 124.54 sec. INFO: vacuuming "pg_toast.pg_toast_47206" INFO: index "pg_toast_47206_index" now contains 550 row versions in 11927 pages DETAIL: 1415130 index row versions were removed. 11901 index pages have been deleted, 6522 are currently reusable. CPU 1.45s/2.15u sec elapsed 20.62 sec. INFO: "pg_toast_47206": removed 1415130 row versions in 353787 pages DETAIL: CPU 56.92s/32.12u sec elapsed 592.18 sec. INFO: "pg_toast_47206": found 1415130 removable, 114 nonremovable row versions in 353815 pages DETAIL: 114 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 87.42s/43.06u sec elapsed 939.62 sec. INFO: analyzing "incidents.php_sessions" INFO: "php_sessions": scanned 3000 of 110479 pages, containing 0 live rows and 16 dead rows; 0 rows in sample, 0 estimated total rows Total query runtime: 1064249 ms. ----------------------02/24/06 5:00PM---------------------- INFO: vacuuming "incidents.php_sessions" INFO: index "php_sessions_pkey" now contains 4 row versions in 17151 pages DETAIL: 783 index row versions were removed. 17137 index pages have been deleted, 17129 are currently reusable. CPU 0.31s/0.20u sec elapsed 13.89 sec. INFO: "php_sessions": removed 784 row versions in 87 pages DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: "php_sessions": found 784 removable, 3 nonremovable row versions in 110479 pages DETAIL: 0 dead row versions cannot be removed yet. There were 966202 unused item pointers. 0 pages are entirely empty. CPU 1.21s/0.79u sec elapsed 15.82 sec. INFO: vacuuming "pg_toast.pg_toast_47206" INFO: index "pg_toast_47206_index" now contains 310 row versions in 11927 pages DETAIL: 1830 index row versions were removed. 11922 index pages have been deleted, 11915 are currently reusable. CPU 0.18s/0.12u sec elapsed 11.39 sec. INFO: "pg_toast_47206": removed 1830 row versions in 465 pages DETAIL: CPU 0.12s/0.04u sec elapsed 0.25 sec. INFO: "pg_toast_47206": found 1830 removable, 30 nonremovable row versions in 354141 pages DETAIL: 20 dead row versions cannot be removed yet. There were 1414680 unused item pointers. 0 pages are entirely empty. CPU 16.07s/4.46u sec elapsed 200.87 sec. INFO: "pg_toast_47206": truncated 354141 to 312 pages DETAIL: CPU 8.32s/2.57u sec elapsed 699.85 sec. INFO: analyzing "incidents.php_sessions" INFO: "php_sessions": scanned 3000 of 110479 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows Total query runtime: 924084 ms. ----------------------02/26/06 9:30AM---------------------- INFO: vacuuming "incidents.php_sessions" INFO: index "php_sessions_pkey" now contains 1 row versions in 17151 pages DETAIL: 46336 index row versions were removed. 17140 index pages have been deleted, 16709 are currently reusable. CPU 0.20s/0.18u sec elapsed 13.96 sec. INFO: "php_sessions": removed 46343 row versions in 4492 pages DETAIL: CPU 0.25s/0.31u sec elapsed 2.42 sec. INFO: "php_sessions": found 46343 removable, 1 nonremovable row versions in 110479 pages DETAIL: 0 dead row versions cannot be removed yet. There were 948998 unused item pointers. 0 pages are entirely empty. CPU 1.07s/0.90u sec elapsed 17.45 sec. INFO: vacuuming "pg_toast.pg_toast_47206" INFO: index "pg_toast_47206_index" now contains 50 row versions in 11927 pages DETAIL: 125250 index row versions were removed. 11923 index pages have been deleted, 11446 are currently reusable. CPU 0.25s/0.12u sec elapsed 11.79 sec. INFO: "pg_toast_47206": removed 125250 row versions in 31316 pages DETAIL: CPU 2.35s/1.79u sec elapsed 15.68 sec. INFO: "pg_toast_47206": found 125250 removable, 32 nonremovable row versions in 31436 pages DETAIL: 30 dead row versions cannot be removed yet. There were 456 unused item pointers. 0 pages are entirely empty. CPU 4.39s/2.20u sec elapsed 37.92 sec. INFO: analyzing "incidents.php_sessions" INFO: "php_sessions": scanned 3000 of 110479 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows Total query runtime: 55517 ms.
