Re: slow query

On Tue, 25 Feb 2003, Tom Lane wrote:

> Robert Treat <xzilla@users.sourceforge.net> writes:
> > Would it be safe to say that tables with high update rates where the
> > updates do not change the indexed value would not suffer from index
> > bloat?
>
> I would expect not.  If you vacuum often enough to keep the main table
> size under control, the index should stay under control too.

    Yes and No, From what I can work out the index is a tree and if
many updates occus the tree can become unbalanced (eventually it get so
unbalanced its no better than a seq scan.... Its not big its just all the
data is all on one side of the tree. Which is why Reindexing is a good
plan. What is really needed is a quicker way of rebalancing the tree. So
the database notices when the index is unbalanced and picks a new root
node and hangs the old root to that. (Makes for a very intresting
algorithim if I remeber my University lectures....)
    Now I'm trying to sort out a very large static table that I've
just finished updating. I am beginning to think that the quickest way of
sorting it out is to dump and reload it. But I'm trying a do it in place
method. (Of Reindex it, vaccum full analyse) but what is the correct order
to do this in?

Reindex, Vaccum

or

Vaccum, Reindex.

Peter Childs

>
> > For example updates to non-index columns or updates that
> > overwrite, but don't change the value of indexed columns; do these even
> > need to touch the index?
>
> Yes, they do.  Think MVCC.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>