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      [EMAIL PROTECTED]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to