"David Esposito" <pgsql-general@xxxxxxxxxxxxxxxxxxxxx> writes:
> 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)
Well, what these numbers show is that you have 5% to 10% daily turnover
of data in this table (maybe more --- are these two days representative,
do you think)? But anyway, taking that number as gospel, you'd expect
that the table and indexes would settle at about 10% free space
immediately after each VACUUM. That would represent a steady state:
just enough free space to get eaten up till the next VACUUM. The table
itself seems to have stabilized, and the "email_campaign_idx" index as
well -- note the latter didn't grow at all, and its internal free space
is in the 10% ballpark:
> 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.
> 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.
I'm not sure why the other indexes don't seem to have reached their
steady-state 10% free yet. What can you tell us about the patterns
of data being inserted into these various indexes?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
