Обсуждение: Unbalanced Btree Indices ...

How are we handling that now?  I seem to recall someone (Tom?) making some
changes to our btree indices for this ...

For instance, very simplistic example:
                            m                       h          t                     a   l      n   z

If I remove the 'h' record, does that remain an empty bucket forever, or
does, say, the l get propogated up into its place, or?  If I lose 'h' and
'a'?

Basically, at what point, if any, should one do a re-build of their
indexes to clean out the empty buckets?  Or does a vacuum automatically do
this for you?  or vacuum full?

Assuming that a rebuild is required, is there anyway of seeing how the
index is balanced, to know when to do it?

From what I do find in the docs, though, the REINDEX command states:

"The index in question contains a lot of dead index pages that are not
being reclaimed. This can occur with B-tree indexes in PostgreSQL under
certain access patterns. REINDEX provides a way to reduce the space
consumption of the index by writing a new version of the index without the
dead pages. See Section 21.2 for more information."

while Section 21.2 mentions that it is less required in 7.4 ... but
neither talk about what 'access patterns' would be affected ...

Pointers to docs that I'm not finding most acceptable ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

"Marc G. Fournier" <scrappy@postgresql.org> writes:
> Pointers to docs that I'm not finding most acceptable ...

There aren't any, because at the time the 7.4 docs were being prepared,
we didn't have enough field experience to know whether its version of
index compaction would eliminate the practical need for REINDEX or not.
So the docs are intentionally vague.

We still haven't gotten a lot of feedback about the point from 7.4
users.  Maybe no news is good news, or maybe it isn't ...

> Assuming that a rebuild is required, is there anyway of seeing how the
> index is balanced, to know when to do it?

Balance isn't a consideration for b-trees, because every part of the
tree is always the same depth.  The only criterion is whether the
physical size of the index is unreasonable compared to the number of
entries it contains.  "VACUUM VERBOSE" is sufficient to find out about
that, though I suppose it could give you a more direct index loading
figure than it does --- right now you have to do your own counting on
your fingers to estimate the average tuple size.
        regards, tom lane

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> Pointers to docs that I'm not finding most acceptable ...
>
> There aren't any, because at the time the 7.4 docs were being prepared,
> we didn't have enough field experience to know whether its version of
> index compaction would eliminate the practical need for REINDEX or not.
> So the docs are intentionally vague.
>
> We still haven't gotten a lot of feedback about the point from 7.4
> users.  Maybe no news is good news, or maybe it isn't ...

While upgrading from 7.4.1 to 7.4.2 last week, our production system
dropped at least 2GB during a vacuum full, and a reindex following that
also dropped about 2GB. Does that count as index bloat? It "feels" like
it's much better than 7.3.x, but it could also be my imagination. I wasn't
watching the details of the vacuum since I was mostly interested in
whether pg_autovacuum was keeping up and/or whether our FSM settings
needed an increase.

If there's some particular metric for index bloat that you can rattle off
the top of your head, I'll be glad to report back on it in another weekend
or two, when we've had some time to build up some bloat again.