Re: [PERFORM] encouraging index-only scans

From: Jim Nasby <jim(at)nasby(dot)net>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Peter Geoghegan <peter(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PERFORM] encouraging index-only scans
Date: 2013-09-06 20:10:06
Message-ID: 522A369E.7020505@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Nasby 2013-09-06 20:23:38 Re: [HACKERS] Is it necessary to rewrite table while increasing the scale of datatype numeric?
Previous Message Jim Nasby 2013-09-06 20:00:02 Re: [PERFORM] encouraging index-only scans

Browse pgsql-performance by date

  From Date Subject
Next Message Andres Freund 2013-09-06 22:22:41 Re: [PERFORM] encouraging index-only scans
Previous Message Jim Nasby 2013-09-06 20:00:02 Re: [PERFORM] encouraging index-only scans