Re: [PERFORM] encouraging index-only scans

On 9/6/13 2:13 PM, Bruce Momjian wrote:
> 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.

Something else that might be useful to look at is if there are any FSM entries or not. True insert only shouldn't have any FSM.

That said, there's definitely another case to think about... tables that see update activity on newly inserted rows but not on older rows. A work queue that is not pruned would be an example of that:

INSERT new work item
UPDATE work item SET status = 'In process';
UPDATE work item SET completion = '50%';
UPDATE work item SET sattus = 'Complete", completion = '100%';

In this case I would expect most of the pages in the table (except the very end) to be all visible.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net