Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> I think I was thinking that a given table will always report more pages
> than an index on that table, since tables can report 50% empty pages
> while indexes only report 100% empty pages. This would cause tables to
> generally be favored over indexes, even though the index pages have the
> most to gain.
But the other side of that coin is that the table needs the FSM service
more: it will be accepting larger insertions and trying to stuff them
into pages that aren't wholly empty. So it just naturally needs to keep
track of more free pages. (This argument is rigorously true if you
compare a table with its own index; it's probably qualitatively okay
when considering unrelated indexes.) So like I said, I'm really more
concerned that the indexes may be over-favored.
Also keep in mind that, as Steve pointed out, we'll really be allocating
space on a chunk basis not a page basis. For an index there is no need
to store free-space-per-page at all; this means we could fit 48 page
numbers into the same size chunk that normally holds 32 page numbers and
32 free space counts. (I'm not sure I will do this, but I will if it
can be done without uglifying the code excessively.) So if we allocate
space on a proportionate-number-of-chunks basis, the indexes should get
some additional win there.
We could throw in a fudge-factor multiplier that discriminates for or
against indexes while choosing how much space to give them. Without any
clear idea where to set it, I'm not eager to do so --- but that'd provide
a solution if it becomes apparent that one side or the other is being
favored too much.
regards, tom lane
In response to
pgsql-hackers by date
|Next:||From: mlw||Date: 2003-02-27 17:27:44|
|Subject: analyze after a database restore?|
|Previous:||From: Robert Treat||Date: 2003-02-27 17:04:07|
|Subject: Re: Free-space-map management thoughts|