Re: Measuring relation free space

On Wed, Jan 18, 2012 at 09:46:20AM -0500, Jaime Casanova wrote:
> On Mon, Jan 16, 2012 at 5:09 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> >
> > pgstattuple()'s decision to treat half-dead pages like deleted pages is
> > better. ?That transient state can only end in the page's deletion.
> >
>
> the only page in that index has 200 records (all live 0 dead) using
> half the page size (which is a leaf page and is not half dead, btw).
> so, how do you justify that pgstattuple say we have just 25% of free
> space?
>
> postgres=# SELECT * from bt_page_stats('pgbench_tellers_pkey', 1);
> -[ RECORD 1 ]-+-----
> blkno | 1
> type | l
> live_items | 200
> dead_items | 0
> avg_item_size | 16
> page_size | 8192
> free_size | 4148
> btpo_prev | 0
> btpo_next | 0
> btpo | 0
> btpo_flags | 3
>
> > I don't know about counting non-leaf pages
>
> ignoring all non-leaf pages still gives a considerable difference
> between pgstattuple and relation_free_space()

pgstattuple() counts the single B-tree meta page as always-full, while
relation_free_space() skips it for all purposes. For tiny indexes, that can
shift the percentage dramatically.