Re: pgstattuple extension for indexes

ITAGAKI Takahiro wrote:
> Satoshi Nagayasu <nagayasus@nttdata.co.jp> wrote:
> 
>> Well, in that way, following two conditions,
>>    [1] [x] [2] [y] [3]
>> and
>>    [3] [x] [2] [y] [1]
>> will be calculated as same fragmentation ratio(100%), I can't agree
>> with that, because both will generate different costs while index scan
>> in the real world (I don't care about page splitting algorithm now).
> 
> I think the calculations (100%) are appropriate, because we should do
> REINDEX in both case. Supposing to the sizes of [x], [y] are mega or giga
> bytes, the order is not important; we have to do large seeks in both case.

I don't think so. A few blocks forward skip while scan can be reasonable
and acceptable (of course, it's case by case).

BTW, What does 'large seeks' mean? Seeking a few blocks, hundred of blocks
and millions of blocks are not same, I think. Are they same for you?

>> However, in such way, if I get '57.6%' as a fragmentation radio,
>> what does it mean? What can I do next?
> 
> I think the information of fragmentations are probably not 
> the most important; the information users want to know are 
> "When to do REINDEX?" and "How to set the fillfactor?".

Agreed.
So, I'm just counting backward seeks simply for the fragmentation ratio.
It means 'the mismatch radio between logical order and physical order
of the blocks'.

> I hope you to write how to interpret the framgentation (and other) info
> in README. In my understanding, I'll write "You'd better do REINDEX when
> you see the fragmentation is greater than 50%" under the present
> calculation method.

I can't understand why you want to make such decision, because you're
thinking the fragmentation information is not the most important for
the users, aren't you?
-- 
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
Phone: +81-3-3523-8122