Re: [PERFORM] encouraging index-only scans
От | Bruce Momjian |
---|---|
Тема | Re: [PERFORM] encouraging index-only scans |
Дата | |
Msg-id | 20130906170159.GB13158@momjian.us обсуждение исходный текст |
Ответ на | Re: [PERFORM] encouraging index-only scans (Andres Freund <andres@2ndquadrant.com>) |
Ответы |
Re: [PERFORM] encouraging index-only scans
(Bruce Momjian <bruce@momjian.us>)
Re: [PERFORM] encouraging index-only scans (Andres Freund <andres@2ndquadrant.com>) |
Список | pgsql-hackers |
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. > > 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. > > 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. 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. > 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. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
В списке pgsql-hackers по дате отправления: