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,
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.
: 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 di
Begin forwarded message:
> From: Yves Vindevogel
> <[EMAIL PROTECTED]>
> Date: Mon 13 Jun 2005 17:45:19 CEST
> To: Tom Lane <[EMAIL PROTECTED]>
> Subject: Re:
un 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.
--
Richar
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 shou
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 ?
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: a
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
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
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, 588
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.
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 expe
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 desc
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
15 matches
Mail list logo
