Re: pgstattuple extension for indexes

On Aug 17, 2006, at 4:10 PM, Martijn van Oosterhout wrote:
> On Thu, Aug 17, 2006 at 02:54:20PM -0500, Jim C. Nasby wrote:
>> On Thu, Aug 17, 2006 at 02:23:48PM +0200, Martijn van Oosterhout
>> wrote:
>>> On Thu, Aug 17, 2006 at 12:55:28PM +0900, 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++;
>>>>
>>>> Do you think which method is better? Or do you have other ideas?
>>
>> Ok, fine... expand the example out to an index that's not trivial in
>> size. Even with read-ahead, once you get to a few megs (which is
>> obviously not that big), you're seeking.
>
> Well, mostly I'm just saying that only matching on the next block
> number is going to give unrealistically low numbers. We can't
> ignore OS
> level caching, the way Postgres works relies on it in many ways.

While I agree that *users* must take caching into account, I don't
think we should be fudging fragmentation numbers. For starters, we
have absolutely no idea how much caching is actually happening.

We should just report the raw numbers and let users draw their own
conclusions. Doing otherwise makes the stat far less useful.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461