Re: [PERFORM] encouraging index-only scans
От | Bruce Momjian |
---|---|
Тема | Re: [PERFORM] encouraging index-only scans |
Дата | |
Msg-id | 20140201032207.GD31141@momjian.us обсуждение исходный текст |
Ответ на | Re: [PERFORM] encouraging index-only scans (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: [PERFORM] encouraging index-only scans
|
Список | pgsql-hackers |
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
В списке pgsql-hackers по дате отправления: