DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=e/91xVyKwUv8RUd7k3qbMUf2dfgHxvHs4Ix4EaBvja7Lh0psqo5zEEVvorsDTvbAdMc3cL8zWUm4QSZKLt1R03s78Sq5eT5nSzlBLpN26FeICEZniQT4ZJHcijwDhlC3ZR3gjOqM6HiXMinls64pvSoZ/fJGGlMPOKzPKZHkTtY=;
References: <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>
	<20130906222623.GC626072@alap2.anarazel.de>
	<20130907002908.GA12753@momjian.us>
	<20130907053449.GE626072@alap2.anarazel.de>	<5238854F.5040708@nasby.net>
	<20130917231050.GD29545@awork2.anarazel.de>
 <CA+TgmoZQyt253OA_ssD6OVs8o0-RkRz3eK6qZz3Utwp-2QVf6A@mail.gmail.com>
Message-ID: <1379626613.94647.YahooMailNeo@web162901.mail.bf1.yahoo.com>
Date: Thu, 19 Sep 2013 14:36:53 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: [PERFORM] encouraging index-only scans
To: Robert Haas <robertmhaas@gmail.com>,
 Andres Freund <andres@2ndquadrant.com>
Cc: Jim Nasby <jim@nasby.net>, Bruce Momjian <bruce@momjian.us>,
 Alvaro Herrera <alvherre@2ndquadrant.com>,
 Peter Geoghegan <peter@2ndquadrant.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
In-Reply-To: 
 <CA+TgmoZQyt253OA_ssD6OVs8o0-RkRz3eK6qZz3Utwp-2QVf6A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

Robert Haas <robertmhaas@gmail.com> wrote:

> Right now, whether or not to autovacuum is the rest of a two-pronged

> test.=A0 The first prong is based on number of updates and deletes
> relative to table size; that triggers a regular autovacuum.=A0 The
> second prong is based on age(relfrozenxid) and triggers a
> non-page-skipping vacuum (colloquially, an anti-wraparound vacuum).
>=20
> The typical case in which this doesn't work out well is when the table
> has a lot of inserts but few or no updates and deletes.=A0 So I propose
> that we change the first prong to count inserts as well as updates and
> deletes when deciding whether it needs to vacuum the table.=A0 We
> already use that calculation to decide whether to auto-analyze, so it
> wouldn't be very novel.=A0  We know that the work of marking pages
> all-visible will need to be done at some point, and doing it sooner
> will result in doing it in smaller batches, which seems generally
> good.
>=20
> However, I do have one concern: it might lead to excessive
> index-vacuuming.=A0 Right now, we skip the index vac step only if there
> ZERO dead tuples are found during the heap scan.=A0 Even one dead tuple
> (or line pointer) will cause an index vac cycle, which may easily be
> excessive.=A0 So I further propose that we introduce a threshold for
> index-vac; so that we only do index vac cycle if the number of dead
> tuples exceeds, say 0.1% of the table size.

+1=A0 I've been thinking of suggesting something along the same lines,
for the same reasons.

=A0
--=20
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers