Re: pgstattuple extension for indexes

ITAGAKI Takahiro wrote:
> But the method has the above problem. So I suggest to use whether
> the right link points to the next adjacent page or not.
>
> if (opaque->btpo_next != P_NONE && opaque->btpo_next != blkno + 1)
> stat->fragments++;

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).

If we think 'fragmentation' more strictly, the fragmentation ratio
should be calculated with 'distance' and 'direction' of the block
ordering and positions, because
[1] [x] [y] [z] [2]
and
[2] [x] [y] [1] [z]
have different costs each.

However, in such way, if I get '57.6%' as a fragmentation radio,
what does it mean? What can I do next? Two cases (forward ordered blocks
with some gaps, and backward ordered blocks with some gaps) are clearly
different, but will result same radios.

Understanding and estimating real cost of the index scan is difficult.
So I want to think 'fragmentation radio' simply,
"How many backward seeks will occur while your index scan?".

I guess, in some cases, people will want to know more detailed information,
but most people need a tool which is easy to use and easy to understand.
And I believe present calculation is good enough.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
Phone: +81-3-3523-8122