Tom Lane wrote: > * We also need to think harder about when to invoke the page pruning > code. As the patch stands, if you set a breakpoint at > heap_page_prune_opt it'll seem to be hit constantly (eg, once for every > system catalog probe), which seems uselessly often. And yet it also > seems not often enough, because one thing I found out real fast is that > the "prune if free space < 1.2 average tuple size" heuristic fails badly > when you look at queries that execute multiple updates within the same > heap page. We only prune when we first pin a particular target page, > and so the additional updates don't afford another chance to see if it's > time to prune. > > I'd like to see if we can arrange to only do pruning when reading a page > that is known to be an update target (ie, never during plain SELECTs); > I suspect this would be relatively easy with some executor and perhaps > planner changes. But that only fixes the first half of the gripe above; > I'm not at all sure what to do about the multiple-updates-per-page > issue.
There is one wacky idea I haven't dared to propose yet: We could lift the limitation that you can't defragment a page that's pinned, if we play some smoke and mirrors in the buffer manager. When you prune a page, make a *copy* of the page you're pruning, and keep both versions in the buffer cache. Old pointers keep pointing to the old version. Any new calls to ReadBuffer will return the new copy, and the old copy can be dropped when its pin count drops to zero. Tracking multiple copies of a page requires some changes to the buffer manager. LockBuffer would need to return the latest version of the page, because anything that checks visibility or does updates would need to use the latest copy, and callers of LockBuffer would need to be changed accordingly. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate