Thread: Fwd: Updates on large tables are extremely slow
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.
Attachment
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
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>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 <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.
Attachment
> Ok, if all 21 are affected, I can understand the problem. > But allow me to say that this is a "functional error" It's a choice between total throughput on a high load, high connection basis (MVCC dramatically wins here), versus a single user, low load scenario (MS Access is designed for this). Believe me when I say that a lot of people have spent a lot of time explicitly making the system work that way. > 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 > > > > ______________________________________________________________________ > > > > 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 --
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.
Attachment
Hi, At 19:22 13/06/2005, Yves Vindevogel wrote: >It can't be indexes on other tables, right ? It could be foreign keys from that table referencing other tables or foreign keys from other tables referencing that table, especially if you don't have the matching indexes... Jacques.
> Ok, if all 21 are affected, I can understand the problem. > But allow me to say that this is a "functional error" No, it's normal MVCC design...