pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdates on large tables are extremely slow
15+ messages / 7 participants
[nested] [flat]
* Updates on large tables are extremely slow
@ 2005-06-12 17:40 Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-12 17:57 ` Re: Updates on large tables are extremely slow Jacques Caron <jc@directinfos.com>
2005-06-13 02:43 ` Re: Updates on large tables are extremely slow Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 2 replies; 15+ messages in thread
From: Yves Vindevogel @ 2005-06-12 17:40 UTC (permalink / raw)
To: pgsql-performance
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)
The query takes about half an hour to an hour to execute. I have tried
a lot of things.
This is my setup
Linux Slackware 10.1
Postgres 8.0.1
My filesystem has EXT2 filesystem so I don't have journaling.
My partition is mounted in fstab with the noatime option.
I have tried to change some settings in $PGDATA/postgresql.conf. But
that does not seem to matter a lot.
I'm not even sure that file is being used. I ran KSysGuard when
executing my query and I don't see my processor being used more than
20%
The memory increases for the cache, but not for the app itself.
My testsystem is an Asus portable, P4 with 1 Gig of RAM.
Disk is speedy. All runs fine except for the update queries.
I would appreciate some help or a document to point me to the settings
I must change.
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Updates on large tables are extremely slow
2005-06-12 17:40 Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-12 17:57 ` Jacques Caron <jc@directinfos.com>
1 sibling, 0 replies; 15+ messages in thread
From: Jacques Caron @ 2005-06-12 17:57 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Updates on large tables are extremely slow
2005-06-12 17:40 Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-13 02:43 ` Mark Kirkwood <markir@paradise.net.nz>
2005-06-13 08:54 ` Re: Updates on large tables are extremely slow Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 1 reply; 15+ messages in thread
From: Mark Kirkwood @ 2005-06-13 02:43 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Updates on large tables are extremely slow
2005-06-12 17:40 Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 02:43 ` Re: Updates on large tables are extremely slow Mark Kirkwood <markir@paradise.net.nz>
@ 2005-06-13 08:54 ` Mark Kirkwood <markir@paradise.net.nz>
2005-06-13 09:02 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 15+ messages in thread
From: Mark Kirkwood @ 2005-06-13 08:54 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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' ?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Updates on large tables are extremely slow
2005-06-12 17:40 Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 02:43 ` Re: Updates on large tables are extremely slow Mark Kirkwood <markir@paradise.net.nz>
2005-06-13 08:54 ` Re: Updates on large tables are extremely slow Mark Kirkwood <markir@paradise.net.nz>
@ 2005-06-13 09:02 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 14:32 ` Re: Updates on large tables are extremely slow Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Yves Vindevogel @ 2005-06-13 09:02 UTC (permalink / raw)
To: pgsql-performance
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 19382 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 75443 unused item pointers.
0 pages are entirely empty.
CPU 3.43s/6.83u sec elapsed 97.86 sec.
INFO: vacuuming "pg_toast.pg_toast_2169880"
INFO: index "pg_toast_2169880_index" now contains 0 row versions in 1
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_2169880": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
rvponp=#
On 13 Jun 2005, at 10:54, Mark Kirkwood wrote:
> 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' ?
>
>
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Updates on large tables are extremely slow
2005-06-12 17:40 Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 02:43 ` Re: Updates on large tables are extremely slow Mark Kirkwood <markir@paradise.net.nz>
2005-06-13 08:54 ` Re: Updates on large tables are extremely slow Mark Kirkwood <markir@paradise.net.nz>
2005-06-13 09:02 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-13 14:32 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2005-06-13 14:32 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Fwd: Updates on large tables are extremely slow
@ 2005-06-13 11:51 Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 12:17 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 15+ messages in thread
From: Yves Vindevogel @ 2005-06-13 11:51 UTC (permalink / raw)
To: pgsql-performance
>
> 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 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 19382 pages
>> DETAIL: 0 dead row versions cannot be removed yet.
>> There were 75443 unused item pointers.
>> 0 pages are entirely empty.
>> CPU 3.43s/6.83u sec elapsed 97.86 sec.
>> INFO: vacuuming "pg_toast.pg_toast_2169880"
>> INFO: index "pg_toast_2169880_index" now contains 0 row versions in
>> 1 pages
>> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>> INFO: "pg_toast_2169880": found 0 removable, 0 nonremovable row
>> versions in 0 pages
>> DETAIL: 0 dead row versions cannot be removed yet.
>> There were 0 unused item pointers.
>> 0 pages are entirely empty.
>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>> VACUUM
>> rvponp=#
>>
>>
>>
>> On 13 Jun 2005, at 10:54, Mark Kirkwood wrote:
>>
>>> 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' ?
>>>
>>>
>> 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 7: don't forget to increase your free space map settings
>>
> 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.
>
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Updates on large tables are extremely slow
2005-06-13 11:51 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-13 12:17 ` Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 15+ messages in thread
From: Yves Vindevogel @ 2005-06-13 12:17 UTC (permalink / raw)
To: pgsql-performance
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 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 19382 pages
>>> DETAIL: 0 dead row versions cannot be removed yet.
>>> There were 75443 unused item pointers.
>>> 0 pages are entirely empty.
>>> CPU 3.43s/6.83u sec elapsed 97.86 sec.
>>> INFO: vacuuming "pg_toast.pg_toast_2169880"
>>> INFO: index "pg_toast_2169880_index" now contains 0 row versions in
>>> 1 pages
>>> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
>>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>>> INFO: "pg_toast_2169880": found 0 removable, 0 nonremovable row
>>> versions in 0 pages
>>> DETAIL: 0 dead row versions cannot be removed yet.
>>> There were 0 unused item pointers.
>>> 0 pages are entirely empty.
>>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>>> VACUUM
>>> rvponp=#
>>>
>>>
>>>
>>> On 13 Jun 2005, at 10:54, Mark Kirkwood wrote:
>>>
>>>> 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' ?
>>>>
>>>>
>>> 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 7: don't forget to increase your free space map settings
>>>
>> 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.
>>
>>
> 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 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Fwd: Updates on large tables are extremely slow
@ 2005-06-13 15:49 Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:02 ` Re: Fwd: Updates on large tables are extremely slow Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 15+ messages in thread
From: Yves Vindevogel @ 2005-06-13 15:49 UTC (permalink / raw)
To: pgsql-performance
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
>
>
>
> 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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Fwd: Updates on large tables are extremely slow
2005-06-13 15:49 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-13 16:02 ` Richard Huxton <dev@archonet.com>
2005-06-13 16:45 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 15+ messages in thread
From: Richard Huxton @ 2005-06-13 16:02 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Updates on large tables are extremely slow
2005-06-13 15:49 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:02 ` Re: Fwd: Updates on large tables are extremely slow Richard Huxton <dev@archonet.com>
@ 2005-06-13 16:45 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:53 ` Re: Updates on large tables are extremely slow Rod Taylor <pg@rbt.ca>
2005-06-13 17:22 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-14 01:29 ` Re: Updates on large tables are extremely slow Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 3 replies; 15+ messages in thread
From: Yves Vindevogel @ 2005-06-13 16:45 UTC (permalink / raw)
To: pgsql-performance
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Updates on large tables are extremely slow
2005-06-13 15:49 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:02 ` Re: Fwd: Updates on large tables are extremely slow Richard Huxton <dev@archonet.com>
2005-06-13 16:45 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-13 16:53 ` Rod Taylor <pg@rbt.ca>
2 siblings, 0 replies; 15+ messages in thread
From: Rod Taylor @ 2005-06-13 16:53 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
> 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
--
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Updates on large tables are extremely slow
2005-06-13 15:49 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:02 ` Re: Fwd: Updates on large tables are extremely slow Richard Huxton <dev@archonet.com>
2005-06-13 16:45 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-13 17:22 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 17:36 ` Re: Updates on large tables are extremely slow Jacques Caron <jc@directinfos.com>
2 siblings, 1 reply; 15+ messages in thread
From: Yves Vindevogel @ 2005-06-13 17:22 UTC (permalink / raw)
To: 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:
> 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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Updates on large tables are extremely slow
2005-06-13 15:49 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:02 ` Re: Fwd: Updates on large tables are extremely slow Richard Huxton <dev@archonet.com>
2005-06-13 16:45 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 17:22 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-13 17:36 ` Jacques Caron <jc@directinfos.com>
0 siblings, 0 replies; 15+ messages in thread
From: Jacques Caron @ 2005-06-13 17:36 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Updates on large tables are extremely slow
2005-06-13 15:49 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:02 ` Re: Fwd: Updates on large tables are extremely slow Richard Huxton <dev@archonet.com>
2005-06-13 16:45 ` Re: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-06-14 01:29 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 15+ messages in thread
From: Christopher Kings-Lynne @ 2005-06-14 01:29 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
> 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...
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2005-06-14 01:29 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-12 17:40 Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-12 17:57 ` Jacques Caron <jc@directinfos.com>
2005-06-13 02:43 ` Mark Kirkwood <markir@paradise.net.nz>
2005-06-13 08:54 ` Mark Kirkwood <markir@paradise.net.nz>
2005-06-13 09:02 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 14:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 11:51 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 12:17 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 15:49 Fwd: Updates on large tables are extremely slow Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:02 ` Richard Huxton <dev@archonet.com>
2005-06-13 16:45 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 16:53 ` Rod Taylor <pg@rbt.ca>
2005-06-13 17:22 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 17:36 ` Jacques Caron <jc@directinfos.com>
2005-06-14 01:29 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox