Re: index bloat

As promised, here are two runs of VACUUM VERBOSE on the problem table ...
There was a lot of activity on the campaign_email table on Friday
(Saturday's VACUUM) as compared with Monday (Tuesday's VACUUM)

Thanks,
Dave

VACUUM VERBOSE from 1:30am Saturday July 9

INFO: vacuuming "xxx.campaign_email"
INFO: index "campaign_email_pkey" now contains 5881034 row versions in
41637 pages
DETAIL: 501822 index row versions were removed.
541 index pages have been deleted, 208 are currently reusable.
CPU 2.82s/4.36u sec elapsed 159.53 sec.
INFO: index "email_campaign_patron_unq" now contains 5881111 row versions
in 70329 pages
DETAIL: 501822 index row versions were removed.
819 index pages have been deleted, 310 are currently reusable.
CPU 4.95s/4.72u sec elapsed 328.90 sec.
INFO: index "email_patron_idx" now contains 5881154 row versions in 38110
pages
DETAIL: 501822 index row versions were removed.
46 index pages have been deleted, 4 are currently reusable.
CPU 3.45s/6.04u sec elapsed 213.03 sec.
INFO: index "email_campaign_idx" now contains 5881215 row versions in 31435
pages
DETAIL: 501822 index row versions were removed.
2016 index pages have been deleted, 896 are currently reusable.
CPU 2.33s/3.61u sec elapsed 179.46 sec.
INFO: index "campaign_email_referral_idx" now contains 5881219 row versions
in 31229 pages
DETAIL: 501822 index row versions were removed.
816 index pages have been deleted, 408 are currently reusable.
CPU 2.15s/4.10u sec elapsed 151.37 sec.
INFO: "campaign_email": removed 501822 row versions in 48373 pages
DETAIL: CPU 3.73s/5.55u sec elapsed 121.64 sec.
INFO: "campaign_email": found 501822 removable, 5880995 nonremovable row
versions in 93960 pages
DETAIL: 2 dead row versions cannot be removed yet.
There were 343940 unused item pointers.
0 pages are entirely empty.
CPU 21.75s/28.97u sec elapsed 1177.92 sec.
INFO: analyzing "patronmail.campaign_email"
INFO: "campaign_email": scanned 3000 of 93960 pages, containing 186577 live
rows and 7 dead rows; 3000 rows in sample, 5843592 estimated total rows

VACUUM VERBOSE from 1:30am Tuesday July 12

INFO: vacuuming "patronmail.campaign_email"
INFO: index "campaign_email_pkey" now contains 5583688 row versions in
42653 pages
DETAIL: 280860 index row versions were removed.
1539 index pages have been deleted, 1181 are currently reusable.
CPU 3.06s/3.71u sec elapsed 153.73 sec.
INFO: index "email_campaign_patron_unq" now contains 5583735 row versions
in 72158 pages
DETAIL: 280860 index row versions were removed.
2320 index pages have been deleted, 1786 are currently reusable.
CPU 4.94s/4.69u sec elapsed 329.98 sec.
INFO: index "email_patron_idx" now contains 5583779 row versions in 38226
pages
DETAIL: 280860 index row versions were removed.
174 index pages have been deleted, 171 are currently reusable.
CPU 3.08s/5.51u sec elapsed 212.61 sec.
INFO: index "email_campaign_idx" now contains 5583831 row versions in 31435
pages
DETAIL: 280860 index row versions were removed.
3266 index pages have been deleted, 2531 are currently reusable.
CPU 2.05s/3.27u sec elapsed 182.20 sec.
INFO: index "campaign_email_referral_idx" now contains 5583840 row versions
in 31632 pages
DETAIL: 280860 index row versions were removed.
1131 index pages have been deleted, 726 are currently reusable.
CPU 2.84s/4.17u sec elapsed 155.22 sec.
INFO: "campaign_email": removed 280860 row versions in 48536 pages
DETAIL: CPU 4.23s/5.11u sec elapsed 133.15 sec.
INFO: "campaign_email": found 280860 removable, 5583666 nonremovable row
versions in 93960 pages
DETAIL: 1 dead row versions cannot be removed yet.
There were 862562 unused item pointers.
0 pages are entirely empty.
CPU 22.22s/26.92u sec elapsed 1200.48 sec.
INFO: analyzing "patronmail.campaign_email"
INFO: "campaign_email": scanned 3000 of 93960 pages, containing 178601 live
rows and 4 dead rows; 3000 rows in sample, 5593783 estimated total rows

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, July 08, 2005 9:52 AM
> To: David Esposito
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] index bloat
>
> "David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> > Hmm, how are you getting 1/6? The ballpark seems to be
> about 50% or more for
> > those first 4 ...
>
> Ooops, I got confused about which column was which.
>
> Could we see the results of "vacuum verbose" on this table? Even
> better, verbose output from two successive nightly runs?
>
> regards, tom lane
>