Re: Alter/update large tables - VERRRY annoying behaviour!

On Mon, 15 Apr 2002 13:07:20 -0400
"Dmitry Tkach" <dmitry(at)openratings(dot)com> wrote:
> Hi, everybody!

Hi Dmitry! Don't cross-post! It's annoying!

> This took me awfully long, but worked (I guess).
> I say 'I guess', because I wasn't able so far to verify that - when I triued to do
>
> select * from a limit 1;
>
> It just hungs on me ... at least, it looks like it does.

This didn't hang, it just requires a sequential scan of the whole table.
As you observe below, it will also need to scan through dead tuples,
but that is just a product of MVCC and there's no real way around
it. Once you VACUUM the dead tuples will be removed and sequential
scans should be fast once more.

And before assuming that something has hung, it's a good idea to
look at the output of EXPLAIN for that query, as well as monitoring
system performance (through top, vmstat, etc) to see what the
system is doing.

> Lucky me, I have compiled the backend from sources with full debug info, because if I hadn't done that,
> (as most users), I would certainly had thought, that my database is hopelessly corrupted, and would have to
> recreate it from scratch :-(

That's a ludicrous conclusion.

> First of all, a question for you - is ANY update to a table equivalent (in this respect) to a delete+insert?

Yes, AFAIK -- MVCC requires this.

> - Vacuum, isn't the speediest thing in the world too (it's been running for a hour now, and still has not finished).

Is this 7.2? If not, VACUUM should be substantially faster in 7.2.
In any case, you'll always want to VACUUM or VACUUM FULL (and
ANALYZE) when you change your tables in such a dramatic fashion.

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC