Re: Update table is much faster after copying it
От | Andreas Kretschmer |
---|---|
Тема | Re: Update table is much faster after copying it |
Дата | |
Msg-id | 20150121061403.GA7882@tux обсуждение исходный текст |
Ответ на | Update table is much faster after copying it (Miles Jordan <miles.jordan@rea-group.com>) |
Список | pgsql-general |
Miles Jordan <miles.jordan@rea-group.com> wrote: > Hi, > > I have tables a and b each with around 12 million rows. I am running > an update query to set the id of table b as an attribute of table a, > and have an appropriate index on table b that EXPLAIN tells me should > be used. > > The query takes 6 hours on an AWS db.r3.4xlarge. That seemed hugely > excessive, so I copied table a into a new table using CREATE TABLE b > AS SELECT * FROM a, and for completeness also added the same indexes > and constraints. > > Now, when I run the update on table a, it finishes in 3 minutes, and > produces the same result. > > Can anyone shed some light on why this might happen? I’ve played there are 2 possible reasons: caching and maybe a lot of dead tuples within the original table. Please check the on-disk-size of table a and b. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
В списке pgsql-general по дате отправления: