update and IN vs. EXISTS

Поиск
Список
Период
Сортировка
От pginfo
Тема update and IN vs. EXISTS
Дата
Msg-id 3E3BB210.5C51478C@t1.unisoftbg.com
обсуждение исходный текст
Ответы vacuum and serial primary keys  (Carmen Marincu <marincuc@eeng.dcu.ie>)
Re: update and IN vs. EXISTS  (Bruno Wolff III <bruno@wolff.to>)
Список 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 по дате отправления:

Предыдущее
От: Bruno Wolff III
Дата:
Сообщение: Re: Controlling access to Sequences
Следующее
От: Carmen Marincu
Дата:
Сообщение: vacuum and serial primary keys