On Thu, Mar 17, 2011 at 4:17 AM, Jesper Krogh <jes...@krogh.cc> wrote: > Is it obvious that the visibillity map bits should track complete > pages and not individual tuples? If the visibillity map tracks at > page-level the benefit would fall on "slim tables" where you squeeze > 200 tuples into each page and having an update rate of 1% would > lower the likelyhood even more. (it may be that for slim tables the > index-only-scans are not as benefitial as to wide tables).
I'm not sure exactly what MaxHeapTuplesPerPage works out to be, but say it's 200. If you track visibility info per tuple rather than per page, then the size of the visibility map is going to expand by a factor of 200. That might decrease contention, but otherwise it's a bad thing - the whole point of having the visibility map in the first place is that it's much, much smaller than the heap. If it were the same size as the heap, we could just read the heap. What the map attempts to accomplish is to allow us, by reading a small number of pages, to check whether the tuples we're thinking of reading are likely to be all-visible without actually looking at them. > In collaboration with a vacuuming discussion, I dont know if it > is there allready but how about "opportunistic vacuuming". Say > you have a page what due to changes in one of the tuples are > being written out, will it, while being written out anyway get the > other tuples on the page vacuumed? The really lazy kind of vacuuming I'm talking about could be done this way. Regular vacuuming cannot, because you can't actually prune a dead tuple until you've scanned all the indexes for references to that CTID. The obvious place to do this would be the background writer: if the page is dirty anyway and we're about to evict it, we could decide to (1) set hint bits, (2) set visibility map bits, (3) freeze tuples that need freezing, and (4) identify dead tuples and reclaim the space they use, but not the associated line pointer. Sadly, I'm not sure this would help much. If we have, say, a 4MB relation, you're not even going to notice it when vacuum comes along and does its thing. Even a vacuum freeze is chump change. The problem is with big relations, like say 1GB+. Processing the whole table at once can have a material impact on system performance, so it'd be nice to do some work incrementally. But it's likely that doing it opportunistically as you evict things from shared buffers is only going to help here and there. Even if you optimistically assumed that we could opportunistically do 10% of the vacuuming that way, that's still not much of a dent. And I think it'd probably be less than that in most real-world cases. A further problem is that the background writer is already a pretty busy process, and giving it more things to do isn't very appealing from the standpoint of overall system performance. > It actually dont have to hook into the process directly to benefit > the IO-usage, if it just can get the opportunity to do it before > the page gets evicted from the OS-cache, then it would save a > second read on that page, but it seems way harder to do something > sane around that assumption. Yeah. > Really lazy vacuums would "only" benefit "really static tables" ? where > vacuuming is not that big a problem in the first place. I think the benefit would be tables that are either quite large (where the ability to do this incrementally would be an advantage over regular VACUUM) or insert-only (where we currently have no way to get PD_ALL_VISIBLE bits set without user intervention). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
