Обсуждение: Where is the decision about placement of new tuple made ?
Where in the source is the decision about the placement new tuple (on which page to put it) made ? I'd like to take a look at adding "gravity" to that decision, do that I can make postgres to decide to place new tuple (inserted or updated) near the beginning of file, in order to make it possible for ordinary (lazy) vacuum to shrink relations more often, initially controlled by GUC, maybe later by some other, more automatic hints, like % of empty pages. My current jedi mindtricks (seqscan + update pk=pk on last tuple until its page nr in ctid changes) for shrinking relations do work, but not as well as I'd like them to ;) -- Hannu Krosing <hannu@skype.net>
On Tue, Jul 12, 2005 at 04:30:04PM +0300, Hannu Krosing wrote: > Where in the source is the decision about the placement new tuple (on > which page to put it) made ? heap_insert and heap_update. They get a page with free space from the FSM, or extend the relation, or --in heap_update case-- try to use the same page. > I'd like to take a look at adding "gravity" to that decision, do that I > can make postgres to decide to place new tuple (inserted or updated) > near the beginning of file, in order to make it possible for ordinary > (lazy) vacuum to shrink relations more often, initially controlled by > GUC, maybe later by some other, more automatic hints, like % of empty > pages. You'll have to modify the FSM code, I guess. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Ni aun el genio muy grande llegaría muy lejos si tuviera que sacarlo todo de su propio interior" (Goethe)
Hannu Krosing <hannu@skype.net> writes:
> Where in the source is the decision about the placement new tuple (on
> which page to put it) made ?
RelationGetBufferForTuple() and the free space map
src/backend/access/heap/hio.c
src/backend/storage/freespace/freespace.c
> I'd like to take a look at adding "gravity" to that decision, do that I
> can make postgres to decide to place new tuple (inserted or updated)
> near the beginning of file,
I have strong doubts about this idea. The existing policy is designed
to reduce contention by having different backends inserting into
different pages.
regards, tom lane
If you're going to do this I would suggest keeping in mind that a similar tactic could be used to help keep a table clustered (I think there may even be a TODO on that). The basic idea there is to use the clustering index to decide the best page to put a tuple on and try and return that page (or one close to it) from the FSM. On Tue, Jul 12, 2005 at 04:30:04PM +0300, Hannu Krosing wrote: > > Where in the source is the decision about the placement new tuple (on > which page to put it) made ? > > I'd like to take a look at adding "gravity" to that decision, do that I > can make postgres to decide to place new tuple (inserted or updated) > near the beginning of file, in order to make it possible for ordinary > (lazy) vacuum to shrink relations more often, initially controlled by > GUC, maybe later by some other, more automatic hints, like % of empty > pages. > > My current jedi mindtricks (seqscan + update pk=pk on last tuple until > its page nr in ctid changes) for shrinking relations do work, but not as > well as I'd like them to ;) > > -- > Hannu Krosing <hannu@skype.net> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"