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

I forgot cc


Begin forwarded message:


<excerpt><bold><color><param>0000,0000,0000</param>From:
</color></bold>Yves Vindevogel <<yves.vindevogel@implements.be>

<bold><color><param>0000,0000,0000</param>Date: </color></bold>Mon 13
Jun 2005 17:45:19 CEST

<bold><color><param>0000,0000,0000</param>To: </color></bold>Tom Lane
<<tgl@sss.pgh.pa.us>

<bold><color><param>0000,0000,0000</param>Subject: </color>Re:
[PERFORM] Updates on large tables are extremely slow

</bold>

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

There's only one index affected !


On 13 Jun 2005, at 16:32, Tom Lane wrote:


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

<excerpt>rvponp=3D# vacuum verbose tblPrintjobs ;

INFO:  vacuuming "public.tblprintjobs"

[ twenty-one different indexes on one table ]

</excerpt>

Well, there's your problem.  You think updating all those indexes is

free?  It's *expensive*.  Heed the manual's advice: avoid creating

indexes you are not certain you need for identifiable commonly-used

queries.


(The reason delete is fast is it doesn't have to touch the indexes ...

the necessary work is left to be done by VACUUM.)


            regards, tom lane



</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller></excerpt>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 !
>
> On 13 Jun 2005, at 16:32, Tom Lane wrote:
>
>> Yves Vindevogel <yves.vindevogel@implements.be> writes:
>>> rvponp=3D# vacuum verbose tblPrintjobs ;
>>> INFO:  vacuuming "public.tblprintjobs"
>>> [ twenty-one different indexes on one table ]
>>
>> Well, there's your problem.  You think updating all those indexes is
>> free?  It's *expensive*.  Heed the manual's advice: avoid creating
>> indexes you are not certain you need for identifiable commonly-used
>> queries.
>>
>> (The reason delete is fast is it doesn't have to touch the indexes ...
>> the necessary work is left to be done by VACUUM.)
>>
>>             regards, tom lane
>>
>>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
<excerpt><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>


</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</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.
>
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.