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 по дате отправления: