Re: [PERFORM] Updates on large tables are extremely slow
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 622972 row versions in 5259 pages DETAIL: 9526 index row versions were removed. 4 index pages have been deleted, 0 are currently reusable. CPU 0.31s/0.36u sec elapsed 5.47 sec. INFO: tblprintjobs: removed 9526 row versions in 307 pages DETAIL: CPU 0.00s/0.06u sec elapsed 0.23 sec. INFO: tblprintjobs: found 9526 removable, 622972 nonremovable row versions in
Re: [PERFORM] Updates on large tables are extremely slow
Apologies - I should have said output of 'VACUUM VERBOSE mytable'. (been using 8.1, which displays dead tuple info in ANALYZE...). Mark Yves Vindevogel wrote: rvponp=# analyze verbose tblPrintjobs ; INFO: analyzing public.tblprintjobs INFO: tblprintjobs: 19076 pages, 3000 rows sampled, 588209 estimated total rows ANALYZE On 13 Jun 2005, at 04:43, Mark Kirkwood wrote: Yves Vindevogel wrote: I'm trying to update a table that has about 600.000 records. The update query is very simple : update mytable set pagesdesc = - pages ; The query takes about half an hour to an hour to execute. I have tried a lot of things. Half an hour seem a bit long - I would expect less than 5 minutes on reasonable hardware. You may have dead tuple bloat - can you post the output of 'ANALYZE VERBOSE mytable' ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Updates on large tables are extremely slow
What else I don't understand is that an update is so slow, whereas this rvponp=# insert into tblTest (id, descpages) select oid, -pages from tblPrintjobs ; INSERT 0 622972 rvponp=# delete from tblTest ; DELETE 622972 rvponp=# takes about 1 minute for the insert, and 5 seconds for the delete. On 13 Jun 2005, at 13:51, Yves Vindevogel wrote: 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
Re: [PERFORM] Updates on large tables are extremely slow
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Updates on large tables are extremely slow
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 [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 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-smaller ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Updates on large tables are extremely slow
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 [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 Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements __ 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 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 -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Updates on large tables are extremely slow
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 [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 Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq 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-smaller ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Updates on large tables are extremely slow
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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Updates on large tables are extremely slow
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... ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Updates on large tables are extremely slow
Hi, At 19:40 12/06/2005, Yves Vindevogel wrote: Hi, I'm trying to update a table that has about 600.000 records. The update query is very simple :update mytable set pagesdesc = - pages ; (I use pagesdesc to avoid problems with sort that have one field in ascending order and one in descending order. That was a problem I had a week ago) An index on (-pages) would probably do exactly what you want without having to add another column. The query takes about half an hour to an hour to execute. Depending on the total size of the table and associated indexes and on your exact setup (especially your hardare), this could be quite normal: the exuctor goes through all rows in the table, and for each, creates a copy with the additional column, updates indexes, and logs to WAL. You might want to look into moving your WAL files (pg_xlog) to a separate disk, increase WAL and checkpoint buffers, add more RAM, add more disks... But as I said, you might not even need to do that, just use an index on an expression... Jacques. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Updates on large tables are extremely slow
Yves Vindevogel wrote: I'm trying to update a table that has about 600.000 records. The update query is very simple : update mytable set pagesdesc = - pages ; The query takes about half an hour to an hour to execute. I have tried a lot of things. Half an hour seem a bit long - I would expect less than 5 minutes on reasonable hardware. You may have dead tuple bloat - can you post the output of 'ANALYZE VERBOSE mytable' ? Cheers Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
