Fwd: [PERFORM] Updates on large tables are extremely slow

[Yves Vindevogel]

I have started this on my testmachine at 11h20.  It's still running and here it's 13h40.

Setup:
Intel P4 2Ghz, 1 Gb ram
ReiserFS 3 (with atime in fstab, which is not optimal)
Slackware 10
PG 7.4

I have the same problems on my OSX and other test machines.

It's frustrating.  Even Microsoft Access is faster !!

On 13 Jun 2005, at 11:02, Yves Vindevogel wrote:

rvponp=# vacuum verbose tblPrintjobs ;
INFO:  vacuuming public.tblprintjobs
INFO:  index pkprintjobs now contains 622972 row versions in 8410 pages
DETAIL:  9526 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.60s/0.31u sec elapsed 31.68 sec.
INFO:  index uxprintjobs now contains 622972 row versions in 3978 pages
DETAIL:  9526 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.15s/0.48u sec elapsed 3.59 sec.
INFO:  index ixprintjobsipaddress now contains 622972 row versions in 2542 pages
DETAIL:  9526 index row versions were removed.
49 index pages have been deleted, 0 are currently reusable.
CPU 0.13s/0.24u sec elapsed 2.57 sec.
INFO:  index ixprintjobshostname now contains 622972 row versions in 2038 pages
DETAIL:  9526 index row versions were removed.
35 index pages have been deleted, 0 are currently reusable.
CPU 0.09s/0.30u sec elapsed 1.14 sec.
INFO:  index ixprintjobsrecordnumber now contains 622972 row versions in 1850 pages
DETAIL:  9526 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.28u sec elapsed 1.51 sec.
INFO:  index ixprintjobseventdate now contains 622972 row versions in 1408 pages
DETAIL:  9526 index row versions were removed.
4 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.24u sec elapsed 2.61 sec.
INFO:  index ixprintjobseventtime now contains 622972 row versions in 1711 pages
DETAIL:  9526 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.12s/0.53u sec elapsed 11.66 sec.
INFO:  index ixprintjobseventcomputer now contains 622972 row versions in 2039 pages
DETAIL:  9526 index row versions were removed.
36 index pages have been deleted, 0 are currently reusable.
CPU 0.12s/0.23u sec elapsed 1.27 sec.
INFO:  index ixprintjobseventuser now contains 622972 row versions in 2523 pages
DETAIL:  9526 index row versions were removed.
19 index pages have been deleted, 0 are currently reusable.
CPU 0.14s/0.24u sec elapsed 1.74 sec.
INFO:  index ixprintjobsloginuser now contains 622972 row versions in 2114 pages
DETAIL:  9526 index row versions were removed.
13 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.32u sec elapsed 4.29 sec.
INFO:  index ixprintjobsprintqueue now contains 622972 row versions in 2201 pages
DETAIL:  9526 index row versions were removed.
30 index pages have been deleted, 0 are currently reusable.
CPU 0.10s/0.34u sec elapsed 1.92 sec.
INFO:  index ixprintjobsprintport now contains 622972 row versions in 3040 pages
DETAIL:  9526 index row versions were removed.
40 index pages have been deleted, 0 are currently reusable.
CPU 0.18s/0.27u sec elapsed 2.63 sec.
INFO:  index ixprintjobssize now contains 622972 row versions in 1733 pages
DETAIL:  9526 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.16s/0.43u sec elapsed 4.07 sec.
INFO:  index ixprintjobspages now contains 622972 row versions in 1746 pages
DETAIL:  9526 index row versions were removed.
24 index pages have been deleted, 0 are currently reusable.
CPU 0.13s/0.22u sec elapsed 1.58 sec.
INFO:  index ixprintjobsapplicationtype now contains 622972 row versions in 1395 pages
DETAIL:  9526 index row versions were removed.
27 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.29u sec elapsed 1.20 sec.
INFO:  index ixprintjobsusertype now contains 622972 row versions in 1393 pages
DETAIL:  9526 index row versions were removed.
24 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.22u sec elapsed 0.82 sec.
INFO:  index ixprintjobsdocumentname now contains 622972 row versions in 4539 pages
DETAIL:  9526 index row versions were removed.
6 index pages have been deleted, 0 are currently reusable.
CPU 0.24s/0.38u sec elapsed 5.83 sec.
INFO:  index ixprintjobsdesceventdate now contains 622972 row versions in 1757 pages
DETAIL:  9526 index row versions were removed.
4 index pages have been deleted, 0 are currently reusable.
CPU 0.08s/0.25u sec elapsed 1.16 sec.
INFO:  index ixprintjobsdesceventtime now contains 622972 row versions in 1711 pages
DETAIL:  9526 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.18s/0.52u sec elapsed 9.44 sec.
INFO:  index ixprintjobsdescpages now contains 622972 row versions in 1748 pages
DETAIL:  9526 index row versions were removed.
24 index pages have been deleted, 0 are currently reusable.
CPU 0.06s/0.26u sec elapsed 0.94 sec.
INFO:  index ixprintjobspagesperjob now contains 

Fwd: [PERFORM] Updates on large tables are extremely slow

[Yves Vindevogel]

I forgot cc

Begin forwarded message:

From: Yves Vindevogel [EMAIL PROTECTED]>
Date: Mon 13 Jun 2005 17:45:19 CEST
To: Tom Lane [EMAIL PROTECTED]>
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 [EMAIL PROTECTED]> 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

inline: Pasted Graphic 2.tiff

Mail: [EMAIL PROTECTED]  - Mobile: +32 (478) 80 82 91

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

Web: http://www.implements.be
x-tad-smaller
First they ignore you.  Then they laugh at you.  Then they fight you.  Then you win.
Mahatma Ghandi./x-tad-smallerMet vriendelijke groeten,
Bien à vous,
Kind regards,

Yves Vindevogel
Implements

inline: Pasted Graphic 2.tiff

Mail: [EMAIL PROTECTED]  - Mobile: +32 (478) 80 82 91

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

Web: http://www.implements.be
x-tad-smaller
First they ignore you.  Then they laugh at you.  Then they fight you.  Then you win.
Mahatma Ghandi./x-tad-smaller

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

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

[Richard Huxton]

Yves Vindevogel wrote:

I forgot cc

Begin forwarded message:


From: Yves Vindevogel [EMAIL PROTECTED]
Date: Mon 13 Jun 2005 17:45:19 CEST
To: Tom Lane [EMAIL PROTECTED]
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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly