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:22:41 +0200
Message-ID: <20130906222241.GB626072@alap2.anarazel.de> (raw)
In-Reply-To: <20130906170159.GB13158@momjian.us>
References: <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>
<20130906163056.GA13158@momjian.us>
<20130906163647.GA626072@alap2.anarazel.de>
<20130906170159.GB13158@momjian.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2013-09-06 13:01:59 -0400, Bruce Momjian wrote:
> On Fri, Sep 6, 2013 at 06:36:47PM +0200, Andres Freund wrote:
> > On 2013-09-06 12:30:56 -0400, Bruce Momjian wrote:
> > > > 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.
> >
> > Yep. Although adding the table back into the fsm will lead to it being
> > used for new writes again...
>
> You mean adding _pages_ back into the table's FSM? Yes, that is going
> to cause those pages to get dirty, but it is better than expanding the
> table size. I don't see why you would not update the FSM.
You're right, we should add them, I wasn't really questioning that. I
was, quietly so you couldn't hear it, wondering whether we should
priorize the target buffer selection differently.
> > > 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.
> >
> > Well, older tables will get vacuumed due to vacuum_freeze_table_age. So
> > at some point they will get vacuumed and the vm bits will get set.
>
> Hmm, good point. That would help with an insert-only workload, as long
> as you can chew through 200M transactions. That doesn't help with a
> read-only workload as we don't consume transction IDs for SELECT.
It's even 150mio. For the other workloads, its pretty "common" wisdom to
VACUUM after bulk data loading. I think we even document that.
> > > The more I look at this the worse it appears. How has this gone
> > > unaddressed for over a year?
> >
> > It's been discussed several times including during the introduction of
> > the feature. I am a bit surprised about the panickey tone in this
> > thread.
>
> 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.
Yes, TODO maybe missing.
> It bothers me that we spent time developing index-only scans, but have
> significant workloads where it doesn't work, no efforts on improving it,
> and no documentation on manual workarounds. I have not even seen
> discussion on how we are going to improve this. I would like to have
> that discussion now.
It's not like the feature is useless in this case. You just need to
perform an extra operation to activate it. I am not saying we shouldn't
document it better, but it seriously worries me that a useful feature is
depicted as useless because it requires a manual VACUUM in some
circumstances.
> > Yes, we need to overhaul the way vacuum works (to reduce the frequency
> > of rewriting stuff repeatedly) and the way it's triggered (priorization,
> > more trigger conditions) but all these are known things and "just" need
> > somebody with time.
> Based on the work needed to improve this, I would have thought someone
> would have taken this on during 9.3 development.
There has been some discussion about it indirectly via the freezing
stuff. That also would require more "advanced" scheduling.
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: <20130906222241.GB626072@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