Re: Updates on large tables are extremely slow - Mailing list pgsql-performance

I just dropped 19 of the 21 indexes.  I just left the primary key
constraint and my unique index on 3 fields ...

I did a vacuum full and an analyse ....  I just ran the query again
some 20 minutes ago.


Guess what .... It's still running !!


So it's not that much faster for the moment.

I just want to update a single field in one table with a simple value
(negative value of another field)

That can not be that hard ...


Or is it the MVCC that is responsible for this ?


It can't be indexes on other tables, right ?

That would be absolutely sick


On 13 Jun 2005, at 18:45, Yves Vindevogel wrote:


<excerpt>Ok, if all 21 are affected, I can understand the problem.

But allow me to say that this is a "functional error"


On 13 Jun 2005, at 18:02, Richard Huxton wrote:


<excerpt>Yves Vindevogel wrote:

<excerpt>I forgot cc

Begin forwarded message:

<excerpt>From: Yves Vindevogel <<yves.vindevogel@implements.be>

Date: Mon 13 Jun 2005 17:45:19 CEST

To: Tom Lane <<tgl@sss.pgh.pa.us>

Subject: Re: [PERFORM] Updates on large tables are extremely slow


Yes, but if I update one column, why should PG update 21 indexes ?

There's only one index affected !

</excerpt></excerpt>

No - all 21 are affected. MVCC creates a new row on disk.


--

  Richard Huxton

  Archonet Ltd



</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller><<Pasted Graphic 2.tiff><smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91


Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76


Web: http://www.implements.be

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller></excerpt><excerpt>


---------------------------(end of
broadcast)---------------------------

TIP 5: Have you checked our extensive FAQ?


               http://www.postgresql.org/docs/faq


</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>I just dropped 19 of the 21 indexes.  I just left the primary key
constraint and my unique index on 3 fields ...
I did a vacuum full and an analyse ....  I just ran the query again
some 20 minutes ago.

Guess what .... It's still running !!

So it's not that much faster for the moment.
I just want to update a single field in one table with a simple value
(negative value of another field)
That can not be that hard ...

Or is it the MVCC that is responsible for this ?

It can't be indexes on other tables, right ?
That would be absolutely sick

On 13 Jun 2005, at 18:45, Yves Vindevogel wrote:

> Ok, if all 21 are affected, I can understand the problem.
> But allow me to say that this is a "functional error"
>
> On 13 Jun 2005, at 18:02, Richard Huxton wrote:
>
>> Yves Vindevogel wrote:
>>> I forgot cc
>>> Begin forwarded message:
>>>> From: Yves Vindevogel <yves.vindevogel@implements.be>
>>>> Date: Mon 13 Jun 2005 17:45:19 CEST
>>>> To: Tom Lane <tgl@sss.pgh.pa.us>
>>>> Subject: Re: [PERFORM] Updates on large tables are extremely slow
>>>>
>>>> Yes, but if I update one column, why should PG update 21 indexes ?
>>>> There's only one index affected !
>>
>> No - all 21 are affected. MVCC creates a new row on disk.
>>
>> --
>>   Richard Huxton
>>   Archonet Ltd
>>
>>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
> <Pasted Graphic 2.tiff>
>
> Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91
>
> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>
> Web: http://www.implements.be
>
> First they ignore you.  Then they laugh at you.  Then they fight you.
> Then you win.
> Mahatma Ghandi.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,

Yves Vindevogel
Implements

<smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91


Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76


Web: http://www.implements.be

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>



Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91

Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76

Web: http://www.implements.be

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.
Mahatma Ghandi.