Re: Subject: bool / vacuum full bug followup part 2
От | Jeffrey Baker |
---|---|
Тема | Re: Subject: bool / vacuum full bug followup part 2 |
Дата | |
Msg-id | 20020504195217.GC370@noodles обсуждение исходный текст |
Ответ на | Re: Subject: bool / vacuum full bug followup part 2 (Jeffrey Baker <jwbaker@acm.org>) |
Список | pgsql-general |
On Sat, May 04, 2002 at 10:48:47AM -0700, Jeffrey Baker wrote: > On Fri, May 03, 2002 at 03:47:54PM -0400, Tom Lane wrote: > > Scott Marlowe <scott.marlowe@ihs.com> writes: > > > And reclaimed the space. Is that the official way, short of dropping and > > > recreating an index to reclaim its space? Is there a plan to make vacuum > > > reclaim unused space in indexes? > > > > Yes, and yes, but don't hold your breath on the latter part --- that > > TODO item has been around for awhile. And it's gotten harder now that > > we have lazy VACUUM; that means we need to be able to condense indexes > > concurrently with other index operations. > > > > AFAIK there's not a big problem with index growth if the range of index > > keys remains reasonably static. The problem comes in if you have a > > range of values that keeps growing (eg, you are indexing a SERIAL or > > timestamp column). The right end of the btree keeps growing, but > > there's no mechanism to collapse out no-longer-used space at the left > > end. > > Wouldn't that explain the complaints I have about my toast tables > always growing? Because each toast table has an index, and the > above paragraph makes it sound like indexes on serial values grow > all the time, that would imply that table that where tuples live for > windows of time will always be growing. > > Or did I read that incorrectly? Indeed, I did not. Part of the space leak I am seeing is from this: Start. Insert 20,000 tuples of 13KB each. Delete 20,000 tuples. Vacuum full. Goto Start. Toast index grows by ~535 pages or 4.2MB[1] per cycle, even though vacuum is able to truncate the main relation and the toast relation to zero pages. -jwb 1: This implies a page size of 16KB in the index. I expected it to be smaller.
В списке pgsql-general по дате отправления: