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:
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
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
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.
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
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'
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
EST
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
: 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
EST
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
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...
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
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
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
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
15 matches
Mail list logo
