From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Thoughts on maintaining 7.3 |
Date: | 2003-10-03 21:37:52 |
Message-ID: | 200310032137.h93Lbqc06748@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> > I think what Tom is concerned about is that this hasn't been tested
> > enough with big datasets. Also there a little loss of index pages but
> > it's much less (orders of magnitude, I think) than what was before.
> > This is because the index won't shrink "vertically".
>
> The fact that we won't remove levels shouldn't be meaningful at all ---
> I mean, if the index was once big enough to require a dozen btree
> levels, and you delete everything, are you going to be upset that it
> drops to 13 pages rather than 2? I doubt it.
>
> The reason I'm waffling about whether the problem is completely fixed or
> not is that the existing code will only remove-and-recycle completely
> empty btree pages. As long as you have one key left on a page it will
> stay there. So you could end up with ridiculously low percentage-filled
> situations. This could be fixed by collapsing together adjacent
> more-than-half-empty pages, but we ran into a lot of problems trying to
> do that in a concurrent fashion. So I'm waiting to find out if real
> usage patterns have a significant issue with this or not.
Though the new code will put empty index pages into the free-space map,
will it also shrink the index file to remove those pages? For example,
if I have 200M rows in a table, and I delete all of them except 100,
does the index shrink, or the pages just become available for reuse.
With VACUUM FULL, we have a way to shrink the heap. Do we shrink the
index?
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Browne | 2003-10-03 21:39:03 | Re: Dreaming About Redesigning SQL |
Previous Message | Tom Lane | 2003-10-03 20:36:00 | Re: Strange behavior regarding temporary sequences |