| From: | Gunnlaugur Thor Briem <gunnlaugur(at)gmail(dot)com> |
|---|---|
| To: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> |
| Cc: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Kevin Grittner <kgrittn(at)ymail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT |
| Date: | 2015-03-16 21:08:32 |
| Message-ID: | CAPs+M8KZkScA_qj4Gog99zOaDiseYqEKmCi6DStp0TAhGyWKhw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Mon, Mar 16, 2015 at 7:24 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jim Nasby | 2015-03-17 00:06:19 | Re: Performance issues |
| Previous Message | Tomas Vondra | 2015-03-16 20:59:16 | Re: Performance issues |