From: Andres Freund <andres@2ndquadrant.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Geoghegan <peter@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] encouraging index-only scans
Date: Sat, 7 Sep 2013 00:26:23 +0200
Message-ID: <20130906222623.GC626072@alap2.anarazel.de> (raw)
In-Reply-To: <20130906191330.GA13193@momjian.us>
References: <20130904205655.GR21874@momjian.us>
<20130906001437.GA29264@momjian.us>
<CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com>
<20130906020043.GH27195@momjian.us>
<20130906052236.GF6067@eldon.alvh.no-ip.org>
<20130906130854.GB600952@alap2.anarazel.de>
<20130906163056.GA13158@momjian.us>
<20130906163647.GA626072@alap2.anarazel.de>
<20130906170159.GB13158@momjian.us>
<20130906191330.GA13193@momjian.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2013-09-06 15:13:30 -0400, 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.
I wonder if we shouldn't trigger most vacuums (not analyze!) via unset
fsm bits. Perhaps combined with keeping track of RecentGlobalXmin to
make sure we're not repeatedly checking for work that cannot yet be
done.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@2ndquadrant.com, bruce@momjian.us, alvherre@2ndquadrant.com, robertmhaas@gmail.com, peter@2ndquadrant.com, tgl@sss.pgh.pa.us, andrew@dunslane.net, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] encouraging index-only scans
In-Reply-To: <20130906222623.GC626072@alap2.anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox