Re: [PERFORM] encouraging index-only scans - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: [PERFORM] encouraging index-only scans
Date
Msg-id 20130906163056.GA13158@momjian.us
Whole thread Raw
In response to Re: [PERFORM] encouraging index-only scans  (Andres Freund <andres@2ndquadrant.com>)
Responses Re: [PERFORM] encouraging index-only scans
List pgsql-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. +



pgsql-hackers by date:

Previous
From: Greg Stark
Date:
Subject: Re: [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII
Next
From: Greg Stark
Date:
Subject: Re: [HACKERS] Re: [HACKERS] Is it necessary to rewrite table while increasing the scale of datatype numeric?