From: | Peter Childs <blue(dot)dragon(at)blueyonder(dot)co(dot)uk> |
---|---|
To: | |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: slow query |
Date: | 2003-02-25 16:36:11 |
Message-ID: | Pine.LNX.4.44.0302251628020.14194-100000@RedDragon.Childs |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Tue, 25 Feb 2003, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Mello | 2003-02-25 22:44:13 | Faster 'select count(*) from table' ? |
Previous Message | Tom Lane | 2003-02-25 16:07:15 | Re: slow query |