On Sep-26 2003, Fri, 16:28 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Okay, so a pkey index entry will take 32 bytes counting overhead ... > you've got about 10:1 bloat on the stats_min indexes and 2:1 in stats_hr. > Definitely bad :-( > > I expect the bloat is coming from the fact that the interesting range of > "start" changes over time. 7.4 should be able to recycle index space > in that situation, but 7.3 and before can't.
This is how things look with 7.4beta3: INFO: vacuuming "public.stats_min" INFO: index "stats_min_start" now contains 1963394 tuples in 12086 pages DETAIL: 736088 index tuples were removed. 5029 index pages have been deleted, 2411 are currently reusable. CPU 1.07s/2.13u sec elapsed 8.15 sec. INFO: index "stats_min_pkey" now contains 1963520 tuples in 27945 pages DETAIL: 736088 index tuples were removed. 11722 index pages have been deleted, 5603 are currently reusable. CPU 4.18s/5.55u sec elapsed 113.04 sec. INFO: "stats_min": removed 736088 tuples in 9483 pages DETAIL: CPU 1.00s/1.12u sec elapsed 13.68 sec. INFO: "stats_min": found 736088 removable, 1963394 nonremovable tuples in 23890 pages DETAIL: 0 dead tuples cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 7.20s/9.16u sec elapsed 146.28 sec. ... INFO: vacuuming "public.stats_hr" INFO: index "stats_hr_start" now contains 7850557 tuples in 28059 pages DETAIL: 728706 index tuples were removed. 4337 index pages have been deleted, 2035 are currently reusable. CPU 2.66s/4.72u sec elapsed 41.18 sec. INFO: index "stats_hr_pkey" now contains 7852826 tuples in 57657 pages DETAIL: 728706 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 5.26s/5.66u sec elapsed 123.91 sec. INFO: "stats_hr": removed 728706 tuples in 16033 pages DETAIL: CPU 1.51s/1.68u sec elapsed 25.43 sec. INFO: "stats_hr": found 728706 removable, 7848770 nonremovable tuples in 161840 pages DETAIL: 0 dead tuples cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 15.71s/12.88u sec elapsed 213.28 sec. Definitely a tremendous improvement. The "working base" on-disk db size seems to have stabilised at around 2.4 GiB and is only increasing by ~100 MiB a day, which, by my calculations, is roughly proportional to the frequency of data added. (There is still some minor overhead all right, but it's far from serious.) So again it's a big thank-you to the whole postgres development group for yet another nice step forward, especially to you Tom for the "allow btree index compaction and empty page reuse" changeset. Best regards, -- Tomas Szepe <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org