Re: index growth problem

От: Tom Lane
Тема: Re: index growth problem
Дата: ,
Msg-id: 2651.1161212445@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: index growth problem  ("Jim C. Nasby")
Список: pgsql-performance

Скрыть дерево обсуждения

Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Alex Stapleton, )
   Re: Performance Optimization for Dummies 2 - the SQL  (Markus Schaber, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  index growth problem  (Graham Davis, )
   Re: index growth problem  ("Jim C. Nasby", )
    Re: index growth problem  (Graham Davis, )
     Re: index growth problem  ("Jim C. Nasby", )
    Re: index growth problem  (Tom Lane, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
    Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Scott Marlowe, )
   Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
    Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Tom Lane, )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Merlin Moncure", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  ("Jim C. Nasby", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
  Re: Performance Optimization for Dummies 2 - the SQL  (Shaun Thomas, )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )
 Re: Performance Optimization for Dummies 2 - the SQL  ("Carlo Stonebanks", )

"Jim C. Nasby" <> writes:
> On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
>> When I run the same command to find the size after the VACUUM, it hasn't
>> changed.

> That's not really a useful test to see if VACUUM is working. VACUUM can
> only trim space off the end of a relation (index or table), where by
> 'end' I mean the end of the last file for that relation on the
> filesystem. This means it's pretty rare for VACUUM to actually shrink
> files on-disk for tables. This can be even more difficult for indexes (I
> think it's virtually impossible to shrink a B-tree index file).

Right; IIRC, a plain VACUUM doesn't even try to shorten the physical
index file, because of locking considerations.  The important question
is whether space gets recycled properly for re-use within the index.
If the index continues to grow over time, then you might have a problem
with insufficient FSM space (or not vacuuming often enough).

It might be worth pointing out that VACUUM isn't intended to try to
reduce the disk file to the shortest possible length --- the assumption
is that you are doing vacuuming on a regular basis and so the file
length should converge to a "steady state", wherein the internal free
space runs out about the time you do another VACUUM and reclaim some
more space for re-use.  There's not really any point in being more
aggressive than that; we'd just create additional disk I/O when the
filesystem releases and later reassigns space to the file.

Of course, this argument fails in the scenario where you make a large
and permanent reduction in the amount of data in a table.  There are
various hacks you can use to clean up in that case --- use TRUNCATE not
DELETE if you can, or consider using CLUSTER (not VACUUM FULL).  Some
variants of ALTER TABLE will get rid of internal free space, too.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Jeff Davis
Дата:
Сообщение: Re: Postgresql 8.1.4 - performance issues for select on
От: Mark Kirkwood
Дата:
Сообщение: Re: measuring shared memory usage on Windows