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   [EMAIL PROTECTED]
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to