From: Bruce Momjian <bruce@momjian.us>
To: Andres Freund <andres@2ndquadrant.com>
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: Fri, 6 Sep 2013 12:30:56 -0400
Message-ID: <20130906163056.GA13158@momjian.us> (raw)
In-Reply-To: <20130906130854.GB600952@alap2.anarazel.de>
References: <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>
<20130906020043.GH27195@momjian.us>
<20130906052236.GF6067@eldon.alvh.no-ip.org>
<20130906130854.GB600952@alap2.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On Fri, Sep 6, 2013 at 03:08:54PM +0200, Andres Freund wrote:
> On 2013-09-06 01:22:36 -0400, Alvaro Herrera wrote:
> > I think it's shortsighted to keep thinking of autovacuum as just a way
> > to run VACUUM and ANALYZE. We have already discussed work items that
> > need to be done separately, such as truncating the last few empty pages
> > on a relation that was vacuumed recently. We also need to process a GIN
> > index' pending insertion list; and with minmax indexes I will want to
> > run summarization of heap page ranges.
>
> Agreed.
>
> > So maybe instead of trying to think of VM bit setting as part of vacuum,
> > we could just keep stats about how many pages we might need to scan
> > because of possibly needing to set the bit, and then doing that in
> > autovacuum, independently from actually vacuuming the relation.
>
> I am not sure I understand this though. What would be the point to go
> and set all visible and not do the rest of the vacuuming work?
>
> I think triggering vacuuming by scanning the visibility map for the
> number of unset bits and use that as another trigger is a good idea. The
> vm should ensure we're not doing superflous work.
Yes, I think it might be hard to justify a separate VM-set-only scan of
the table. If you are already reading the table, and already checking
to see if you can set the VM bit, I am not sure why you would not also
remove old rows, especially since removing those rows might be necessary
to allow setting VM bits.
Another problem I thought of is that while automatic vacuuming only
happens with high update/delete load, index-only scans are best on
mostly non-write tables, so we have bad behavior where the ideal case
(static data) doesn't get vm-bits set, while update/delete has the
vm-bits set, but then cleared as more update/deletes occur.
The more I look at this the worse it appears. How has this gone
unaddressed for over a year?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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, andres@2ndquadrant.com, 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: <20130906163056.GA13158@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