From: Jim Nasby <jim@nasby.net>
To: Gavin Flower <GavinFlower@archidevsys.co.nz>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>
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: Fri, 06 Sep 2013 15:00:02 -0500
Message-ID: <522A3442.3020507@nasby.net> (raw)
In-Reply-To: <52292FFC.8020708@archidevsys.co.nz>
References: <50C8F1EC.9010801@dunslane.net>
<15321.1355347953@sss.pgh.pa.us>
<50C90154.8060103@dunslane.net>
<50C904DB.9090305@dunslane.net>
<20121213024837.GB22363@momjian.us>
<23298.1355370698@sss.pgh.pa.us>
<CAEYLb_U1dTg=bXkDL=yvFTrMw5oC13ivgZA=jdQXnbgaDJ+e-Q@mail.gmail.com>
<20130904205655.GR21874@momjian.us>
<20130906001437.GA29264@momjian.us>
<CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com>
<52292FFC.8020708@archidevsys.co.nz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 9/5/13 8:29 PM, Gavin Flower wrote:
> How about a 'VACUUM AFTER' command (part of the 'BEGIN' transaction syntax?) that would:
>
> 1. only be valid in a transaction
> 2. initiate a vacuum after the current transaction completed
> 3. defer any vacuum triggered due to other criteria
>
> If the transaction was rolled back: then if there was a pending vacuum, due to other reasons, it would then be actioned.
>
> On normal transaction completion, then if there was a pending vacuum it would be combined with the one in the transaction.
>
> Still would need some method of ensuring any pending vacuum was done if the transaction hung, or took too long.
I *really* like the idea of BEGIN VACUUM AFTER, but I suspect it would be of very limited usefulness if it didn't account for currently running transactions.
I'm thinking we add a vacuum_after_xid field somewhere (pg_class), and instead of attempting to vacuum inside the backend at commit time the transaction would set that field to it's XID unless the field already had a newer XID in it.
autovac would then add all tables where vacuum_after_xid < the oldest running transaction to it's priority list.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
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: jim@nasby.net, GavinFlower@archidevsys.co.nz, robertmhaas@gmail.com, bruce@momjian.us, 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: <522A3442.3020507@nasby.net>
* 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