Re: [PERFORM] encouraging index-only scans

On Fri, Sep 6, 2013 at 01:01:59PM -0400, Bruce Momjian wrote:
> This December 2012 thread by Andrew Dunstan shows he wasn't aware that a
> manual VACUUM was required for index-only scans. That thread ended with
> us realizing that pg_upgrade's ANALYZE runs will populate
> pg_class.relallvisible.
>
> What I didn't see in that thread is an analysis of what cases are going
> to require manual vacuum, and I have seen no work in 9.3 to improve
> that. I don't even see it on the TODO list.

OK, let's start the discussion then. I have added a TODO list:

Improve setting of visibility map bits for read-only and insert-only workloads

So, what should trigger an auto-vacuum vacuum for these workloads?
Rather than activity, which is what normally drives autovacuum, it is
lack of activity that should drive it, combined with a high VM cleared
bit percentage.

It seems we can use these statistics values:

n_tup_ins | bigint
n_tup_upd | bigint
n_tup_del | bigint
n_tup_hot_upd | bigint
n_live_tup | bigint
n_dead_tup | bigint
n_mod_since_analyze | bigint
last_vacuum | timestamp with time zone
last_autovacuum | timestamp with time zone

Particilarly last_vacuum and last_autovacuum can tell us the last time
of vacuum. If the n_tup_upd/n_tup_del counts are low, and the VM set
bit count is low, it might need vacuuming, though inserts into existing
pages would complicate that.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +