update and IN vs. EXISTS
От | pginfo |
---|---|
Тема | update and IN vs. EXISTS |
Дата | |
Msg-id | 3E3BB210.5C51478C@t1.unisoftbg.com обсуждение исходный текст |
Ответы |
vacuum and serial primary keys
Re: update and IN vs. EXISTS |
Список | 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.
В списке pgsql-sql по дате отправления: