Re: Large table update/vacuum PLEASE HELP!

Lincoln Yeoh wrote:

> At 11:20 AM 4/16/02 -0400, Dmitry Tkach wrote:
>
>> select * from a limit 1;
>>
>> Takes about 30 minutes and I have no idea what it is thinking about
>> for so long!
>>
>> If anyone has anyu idea at all what could be the problem, PLEASE HELP!
>
>
> If a is still something like:
>
> create table a
> (
>    id int primary key,
>    some_data int
> );

Well... Not quite.
The whole problem started when I merged a and b tables together - so,
now a looks like:
create table a
(
    id int primary key,
    some_data int,
    some_other_data int
);

(
I added the last column with alter table and populated it with
update a set some_other_data from b where a.id=b.id;
That's when my nightmare started
)

>
> Try:
>
> vacuum analyze;

Yeah... I did that yesterday... It took about 24 hours (!) to run... And
I don't see any difference in the behaviour :-(

>
> explain select * from a order by id limit 1;


Aha... This is an interesting thing to try. Thanks.
Unfortunately, I can't try that right now either! :-(
I am running 'vacuum full' on that table (out of despare), and, despite
what I have repeatedly heard about
vaccums in 7.2, my 'explain' command seems to be hopelessly waiting on a
lock, created by that vacuum
:-(
Is it supposed to be that way.

>
> select * from a order by id limit 1;

As I said, I could not check it right now... My understanding is that
you suggest that I force it to scan the index rather then the table
itself... This very well may help the immediate problem (once it's done
with the vacuum, which I expect some time tomorrow :-()...

BUT, the main issue is that I can't really do a sequentual scan on that
table (not in any reasonable time anyway) the way it is...
I am just used to thinking, that,  sequentual scan of a table is the
most basic operation I can imagine ... if my database is so screwed up
that even that doesn't work, I won't be able to make much use of it
anyway... :-(
Is that right?

> Good luck,

Thanks! :-)

Dima