Re: DB Tuning Notes for comment...

Robert Treat <rtreat(at)webmd(dot)net> writes:
> On Sun, 2002-12-08 at 09:41, Philip Warner wrote:
>> First of all, the free space manager is useless at managing free space if
>> it can not map all relations (including system relations and toast
>> relations). The following query should give the correct ballpark:
>>
>> select count(*) from pg_class where not relkind in ('i','v');

FSM entries aren't needed for sequences either, so more correct is

select count(*) from pg_class where relkind in ('r', 't');

> It should be noted that if you have multiple databases, you'll need to
> get the number of relations minus the system tables for each database,
> then add in the number of system tables.

You're assuming that system tables are shared, which they mostly aren't.
Summing the pg_class count over all databases (or all that get vacuumed,
anyway; you can exclude template0) will be close enough.

BTW, this neglects what seems possibly an important factor: you don't
need FSM entries for tables that are effectively read-only or insert-only
(no deletes or updates). At least in some database designs, that's a
significant number of tables.

However, I suspect that the present FSM code is not very effective at
deciding *which* tables to track if it has too few slots, so Philip's
advice of "make sure there's one for every table" may be the best in the
near term. But we need to work at improving that logic.

I have some uncommitted patches concerning the FSM management heuristics
from Stephen Marshall, which I deemed too late/risky for 7.3, but we
should get something done for 7.4. Anyone interested in playing around
in this area?

regards, tom lane