From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, Jim Nasby <jim(at)nasby(dot)net>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Peter Geoghegan <peter(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PERFORM] encouraging index-only scans |
Date: | 2014-02-01 03:22:07 |
Message-ID: | 20140201032207.GD31141@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
On Thu, Sep 19, 2013 at 02:39:43PM -0400, Robert Haas wrote:
> Right now, whether or not to autovacuum is the rest of a two-pronged
> test. The first prong is based on number of updates and deletes
> relative to table size; that triggers a regular autovacuum. The
> second prong is based on age(relfrozenxid) and triggers a
> non-page-skipping vacuum (colloquially, an anti-wraparound vacuum).
>
> The typical case in which this doesn't work out well is when the table
> has a lot of inserts but few or no updates and deletes. So I propose
> that we change the first prong to count inserts as well as updates and
> deletes when deciding whether it needs to vacuum the table. We
> already use that calculation to decide whether to auto-analyze, so it
> wouldn't be very novel. We know that the work of marking pages
> all-visible will need to be done at some point, and doing it sooner
> will result in doing it in smaller batches, which seems generally
> good.
>
> However, I do have one concern: it might lead to excessive
> index-vacuuming. Right now, we skip the index vac step only if there
> ZERO dead tuples are found during the heap scan. Even one dead tuple
> (or line pointer) will cause an index vac cycle, which may easily be
> excessive. So I further propose that we introduce a threshold for
> index-vac; so that we only do index vac cycle if the number of dead
> tuples exceeds, say 0.1% of the table size.
>
> Thoughts? Let the hurling of rotten tomatoes begin.
Robert, where are we on this? Should I post a patch?
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2014-02-01 03:23:41 | Re: What is happening on buildfarm member crake? |
Previous Message | Fujii Masao | 2014-02-01 03:07:30 | Re: Compression of full-page-writes |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Kincaid | 2014-02-02 23:52:00 | Re: PostgreSQL 9.3.2 Performance tuning for 32 GB server |
Previous Message | Julien Cigar | 2014-01-31 13:55:04 | Re: PostgreSQL 9.3.2 Performance tuning for 32 GB server |