Heikki Linnakangas wrote: > Bruce Momjian wrote: > > Heikki Linnakangas wrote: > >> Russell Smith wrote: > >>> 2. Index cleanup is the most expensive part of vacuum. So doing a > >>> partial vacuum actually means more I/O as you have to do index cleanup > >>> more often. > >> I don't think that's usually the case. Index(es) are typically only a > >> fraction of the size of the table, and since 8.2 we do index vacuums in > >> a single scan in physical order. In fact, in many applications the index > >> is be mostly cached and the index scan doesn't generate any I/O at all. > > > > Are _all_ the indexes cached? I would doubt that. > > Well, depends on your schema, of course. In many applications, yes. > > > Also, for typical > > table, what percentage is the size of all indexes combined? > > Well, there's no such thing as a typical table. As an anecdote here's > the ratios (total size of all indexes of a table)/(size of corresponding > heap) for the bigger tables for a DBT-2 run I have at hand: > > Stock: 1190470/68550 = 6% > Order_line: 950103/274372 = 29% > Customer: 629011 /(5711+20567) = 8% > > In any case, for the statement "Index cleanup is the most expensive part > of vacuum" to be true, you're indexes would have to take up 2x as much > space as the heap, since the heap is scanned twice. I'm sure there's > databases like that out there, but I don't think it's the common case. I agree it index cleanup isn't > 50% of vacuum. I was trying to figure out how small, and it seems about 15% of the total table, which means if we have bitmap vacuum, we can conceivably reduce vacuum load by perhaps 80%, assuming 5% of the table is scanned. -- Bruce Momjian bruce@xxxxxxxxxx EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
