Re: How to do faster DML

Поиск
Список
Период
Сортировка
От Greg Sabino Mullane
Тема Re: How to do faster DML
Дата
Msg-id CAKAnmmLjqVucFywUozDnB495GnK3GRS-XS1o2_s0wxcBR3B4hw@mail.gmail.com
обсуждение исходный текст
Ответ на How to do faster DML  (Lok P <loknath.73@gmail.com>)
Ответы Re: How to do faster DML
Re: How to do faster DML
Список pgsql-general
As a general rule, avoid heavy subselects like that. You don't need to build a full list of duplicates before starting. Another approach:

create table mytable2 (like mytable1);

alter table mytable2 add primary key (id);

insert into mytable2 select * from mytable1 on conflict do nothing;

Given the size of your table, you probably want to divide that up. 
As long as nothing is changing the original table, you could do:

insert into mytable2 select * from mytable1 order by ctid limit 10_000_000 offset 0;
insert into mytable2 select * from mytable1 order by ctid limit 10_000_000 offset 10_000_000;
insert into mytable2 select * from mytable1 order by ctid limit 10_000_000 offset 20_000_000;
etc.

Cheers,
Greg

В списке pgsql-general по дате отправления:

Предыдущее
От: Lok P
Дата:
Сообщение: Re: How to do faster DML
Следующее
От: Francisco Olarte
Дата:
Сообщение: Re: How to do faster DML