Re: Subject: bool / vacuum full bug followup part 2

Tom Lane wrote:
> Scott Marlowe <scott(dot)marlowe(at)ihs(dot)com> writes:
> > On Fri, 3 May 2002, Tom Lane wrote:
> >> Scott Marlowe <scott(dot)marlowe(at)ihs(dot)com> writes:
> > Well, my keys aren't changing and the index is growing like they are.
> >>
> >> Could we see the exact details of your test case?
>
> > Sure. I think I posted most of it here already...
>
> Okay, what I see is that the index on the integer column behaves like I
> would expect: you can update, vacuum, update, vacuum, and it doesn't get
> bigger. But the index on the boolean column does grow. I believe the
> problem is that there are so many equal keys. The reinserted index
> entries are always inserted at the end of the range of matching keys,
> and so there's no opportunity to re-use space within other pages of the
> index. There are only two leaf pages getting the insertions, and so
> nothing to do but split them over and over.

TODO updated:

* Certain indexes will not shrink, e.g. indexes on ever-increasing
columns and indexes with many duplicate keys

> What this really points up, of course, is that making a btree index on
> a boolean column is a pretty foolish thing to do. I'm not particularly
> unhappy about the performance being bad with respect to space usage,
> because the fact of the matter is that performance is going to be bad
> by any measure.

Yes, but we can't expect people to know to use a partial index when they
are indexing a column like bool. (Our partial index code didn't even
work for several years.) (And there are valid reasons to index bool,
like if there are only a few true or false values, as was pointed out.)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026