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

Tom Lane wrote:
> Scott Marlowe <scott.marlowe@ihs.com> writes:
> > On Fri, 3 May 2002, Tom Lane wrote:
> >> Scott Marlowe <scott.marlowe@ihs.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026