From: | pginfo <pginfo(at)t1(dot)unisoftbg(dot)com> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | update and IN vs. EXISTS |
Date: | 2003-02-01 11:40:00 |
Message-ID: | 3E3BB210.5C51478C@t1.unisoftbg.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I have 2 tables Table1 and Table2.
The PK for Table1 is declared as name.
Table 2 have only 1 field and it is also name ( it is indexed).
I will to update all Table1.filedForUpdate for all rows that exists in
Table2.
In Table1 I have ~ 120 000 rows and in Table2 I have ~ 100 000.
If I execute:
update Table1 set fieldForUpdate = 1;
it takes ~ 28 sec. I test it only to know how much time will I need for
all rows.
If I try to execute:
update Table1 set fieldForUpdate = 1 where ID IN (select T2.ID from
Table2);
it is running very slow.
I do not nkow how many time, but I waited ~ 30 min without to get
result.
I tested anoder query:
update Table1 set fieldForUpdate = 1 where ID IN ( select T1.ID from
Table1 T1 where exists (select * select T2.ID from Table2 where
T1.IDS=T2.IDS ));
and it was running > 30 min ( I do not know how many).
And the last query:
update Table1 set fieldForUpdate = 1 from Tablet T1 where EXISTS
(select * select T2.ID from Table2 where T1.IDS=T2.IDS );
and it was also > 30 min.
How can I speed up this update?
I have executed vacuum and vacuum full analyze.
redards,
ivan.
From | Date | Subject | |
---|---|---|---|
Next Message | Carmen Marincu | 2003-02-01 12:45:24 | vacuum and serial primary keys |
Previous Message | Bruno Wolff III | 2003-02-01 06:39:50 | Re: Controlling access to Sequences |