Re: [PATCHES] Resurrecting per-page cleaner for btree

On Jul 26, 2006, at 4:29 PM, Hannu Krosing wrote:
>>> Well the desire for it comes from a very well established need
>>> for dealing
>>> with extremely large tales with relatively small hot spots. The
>>> basic problem
>>> being that currently the cost of vacuum is proportional to the
>>> size of the
>>> table rather than the amount of dead space. There's no link
>>> between those
>>> variables (at least in one direction) and any time they're far
>>> out of whack it
>>> means excruciating pain for the DBA.
>>
>> I thought the suggested solution for that was the dead space map.
>> That
>> way vacuum can ignore parts of the table that havn't changed...
>
> It can ignore parts of the *table* but still has to scan full
> *indexes*.

Even if we stopped right there it would still be a huge win in many
(most?) cases. How often do the indexes on a table comprise even 50%
of the table's size? If indexes are 10% of the table's size, and you
only scan 10% of the table that's dead, you've gone from scanning
1.10*X pages (where X is the number of pages in the table) to 0.20*X
pages. For large tables, that's a drastic improvement. Even in the
50% case, you've gone from 1.5X to .6X (assuming 10% dead space). And
I'm actually ignoring that we currently scan the heap twice; if you
throw that into the mix the argument for doing this is even stronger.

While it would be ideal to eliminate the need to scan the indexes,
I'd certainly rather have the 'half-solution' of not scanning the
entire heap and still scanning the indexes over what we have today.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461