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

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.

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.

regards, tom lane