Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

Поиск
Список
Период
Сортировка
От Gunnlaugur Thor Briem
Тема Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
Дата
Msg-id CAPs+M8KZkScA_qj4Gog99zOaDiseYqEKmCi6DStp0TAhGyWKhw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Список pgsql-performance
On Mon, Mar 16, 2015 at 7:24 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
The other thing you should consider is using TRUNCATE instead of an un-filtered DELETE. It will both be much faster to perform and won't leave any dead rows behind.

Yep, but it does take an ACCESS EXCLUSIVE lock. We want the old table contents to be readable to other sessions while the new table contents are being populated (which can take quite a while), hence we don't use TRUNCATE.

Best of both worlds is to just populate a new table, flip over to that when it's ready, and drop the old one once nobody's referring to it anymore. That way we don't pay the DELETE scan penalty and don't leave dead rows, and also don't lock reads out while we repopulate.

Gulli

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

Предыдущее
От: Tomas Vondra
Дата:
Сообщение: Re: Performance issues
Следующее
От: Jim Nasby
Дата:
Сообщение: Re: Performance issues