Re: pgstatindex

On Fri, Aug 14, 2009 at 06:10:17AM -0700, paulo matadr wrote:
> I have been watching this field (index_size) , and see probable bug on size .
> anybody may explain me how fix it?
> select * from pgstatindex('micromedicao.xfk8_consumo_historico');
> version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
> ---------+------------+-------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
> 2 | 3 | -1332748288 | 81517 | 1219 | 360379 | 0 | 0 | 78.69 | 16.22
>
> Size of xfk8_consumo_historico on database = 2825MB

-1332748288 is what 2962219008 looks like when displayed as in "twos
complement" (i.e. a signed integer) format. 2962219008 is approx 2825MB
and hence the code is probably doing the right thing, just displaying
the result incorrectly. Not entirely sure why it's ending up in the
wrong format as the code looks OK. Strange.

> one more dought about avg_leaf_density , what's this represent?

No certain, but I'd guess it's the amount of useful data stored in the
index. 80% sounds like it's pretty full; there's the classic trade off
between time and space, the more time PG spends trying to keep it full
the more it's got to keep rearranging it, the more empty space there is
the more time it's got to spend looking for the right thing. I don't
really know if 80% is good here, but it doesn't sound bad.

--
Sam http://samason.me.uk/