Re: Measuring relation free space

On Sun, Nov 06, 2011 at 10:20:49PM +0100, Bernd Helmle wrote:
> --On 6. November 2011 01:08:11 -0200 Greg Smith <greg(at)2ndQuadrant(dot)com> wrote:
>
>> Attached patch adds a new function to the pageinspect extension for measuring
>> total free space, in either tables or indexes.
>
> I wonder if that should be done in the pgstattuple module, which output
> some similar numbers.

Indeed, pgstattuple already claims to show precisely the same measure. Its
reckoning is right in line for heaps, but the proposed pageinspect function
finds more free space in indexes:

[local] test=# SELECT t.free_percent, relation_free_space('pg_proc'), i.free_percent, relation_free_space('pg_proc_proname_args_nsp_index') FROM pgstattuple('pg_proc') t, pgstattuple('pg_proc_proname_args_nsp_index') i;
free_percent | relation_free_space | free_percent | relation_free_space
--------------+---------------------+--------------+---------------------
2.53 | 0.0253346 | 8.61 | 0.128041
(1 row)

Is one of those index figures simply wrong, or do they measure two senses of
free space, both of which are interesting to DBAs?

Thanks,
nm