From: Bruce Momjian <bruce@momjian.us>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@2ndquadrant.com>
Cc: Jim Nasby <jim@nasby.net>
Cc: Alvaro Herrera <alvherre@2ndquadrant.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: Tue, 11 Feb 2014 17:40:14 -0500
Message-ID: <20140211224014.GL2289@momjian.us> (raw)
In-Reply-To: <CAGTBQpZNJx_o=UJjwRYZCSKvsn6T8ou0QdufrQBzFvEBSRTebQ@mail.gmail.com>
References: <5238854F.5040708@nasby.net>
<20130917231050.GD29545@awork2.anarazel.de>
<CA+TgmoZQyt253OA_ssD6OVs8o0-RkRz3eK6qZz3Utwp-2QVf6A@mail.gmail.com>
<20140201032207.GD31141@momjian.us>
<CA+TgmoYOAzNKWKdkNcq50MFQKdgSt1ESR6zxb8XKCiNO6YBX2A@mail.gmail.com>
<20140211155607.GB2289@momjian.us>
<CA+TgmobeczQK0LhgVz_bSs7M4iTbCP=sfK8=6yDuCpwtOiuCCw@mail.gmail.com>
<20140211171213.GE2289@momjian.us>
<CAMkU=1xK7yL9SNH2Xprnws2C-fMtgBd07eAQcpDD8Zhj9LJtUQ@mail.gmail.com>
<CAGTBQpZNJx_o=UJjwRYZCSKvsn6T8ou0QdufrQBzFvEBSRTebQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On Tue, Feb 11, 2014 at 05:51:36PM -0200, Claudio Freire wrote:
> > We track relpages and relallvisible, which seems like a more direct measure.
> > Once analyze is done (which is already triggered by inserts) and sets those,
> > it could fire a vacuum based on the ratio of those values, or the autovac
> > process could just look at the ratio after naptime. So just introduce
> > autovacuum_vacuum_visible_factor. A problem there is that it would be a lot
> > of work to aggressively keep the ratio high, and pointless if the types of
> > queries done on that table don't benefit from IOS anyway, or if pages are
> > dirtied so rapidly that no amount of vacuuming will keep the ratio high.
> > Would we try to automatically tell which tables were which, or rely on the
> > DBA setting per-table autovacuum_vacuum_visible_factor for tables that
> > differ from the database norm?
>
>
> Why not track how many times an IOS would be used but wasn't, or how
> many heap fetches in IOS have to be performed?
>
> Seems like a more direct measure of whether allvisible needs an update.
Now that is in interesting idea, and more direct.
Do we need to adjust for the insert count, i.e. would the threadhold to
trigger an autovacuum after finding index lookups that had to check the
heap page for visibility be higher if many inserts are happening,
perhaps dirtying pages? (If we are dirtying via update/delete,
autovacuum will already trigger.)
We are aggressive in clearing the page-all-visible flag (we have to be),
but I think we need a little more aggressiveness for setting it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
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: bruce@momjian.us, klaussfreire@gmail.com, jeff.janes@gmail.com, robertmhaas@gmail.com, andres@2ndquadrant.com, jim@nasby.net, alvherre@2ndquadrant.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: <20140211224014.GL2289@momjian.us>
* 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